I have the following data. I need to get the most recent date. However, I'm unable to exclude both the Error and 1/0/00 at the same time.
I have tried using
=MAX(IF(NOT(ISERROR(B16:E16)),B16:E16)) but that returns 1/0/00 as the MAX.
Also, I have tried
=MAX(IF(B15:E15>1/0/0,B15:E15)) to get rid of the zero but that doesn't quite work either. Also, when I combine the two, it really doesn't work.
In the example below, the output for row 2 under "Date-Max" would be 7/28/16.
Date1 Date2 Date3 Date4 Date-Max 7/26/16 #N/A #N/A 1/0/00 Output 7/20/16 7/28/16 1/0/00 1/0/00 Output 7/25/16 #N/A #N/A 1/0/00 Output 7/25/16 #N/A #N/A 1/0/00 Output 7/20/16 #N/A #N/A 1/0/00 Output 7/25/16 7/29/16 #N/A 1/0/00 Output 7/17/16 #N/A 8/1/16 1/0/00 Output
Need the max output for each row.
Consider data in A1 through A10, use this array formula:
It is not necessary to test for zero, since MAX() will pick any positive value over zero anyway.
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.