Excel MAX IF Function To Ingore N/A Error And Zero Date (1/0/00)

by DomainsFeatured   Last Updated April 24, 2018 02:01 AM

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.



Answers 1


Consider data in A1 through A10, use this array formula:

=MAX(IF(NOT(ISNA(A1:A10)),A1:A10,0))

enter image description here

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.

Gary's Student
Gary's Student
August 03, 2016 19:46 PM

Related Questions


Excel 2016 - Any suggestions on functions?

Updated November 10, 2017 00:01 AM