Ignore #N/A in formula

[Mike D.]

Hi. I want to calculate the maximum number in a series of
numbers. The series in column A might be 93, 94, #N/A,
85. I want to use the MAX(A:A) function to return the
number 94. However, it returns the #N/A. How can I get
it to ignore the #N/A?

Thanks,
Mike

 

[Jason Morin]

=MAX(IF(ISNUMBER(A1:A1000),A1:A1000))

Press ctrl/shift/enter - it's an array formula.

HTH
Jason
Atlanta, GA

 

[Soo Cheon Jheong]

Mike,

Enter the formula as array formula(Press ctrl/shift/enter):

=MAX(IF(ISNA(A1:A1000),0,A1:A1000))

or

=MAX(IF(ISNA(A1:A1000),"",A1:A1000))

or

=MAX(IF(ISERROR(A1:A1000),0,A1:A1000))

or

=MAX(IF(ISERROR(A1:A1000),"",A1:A1000))

 

[Mike Chadwick]

=if(isna(yourformulaehere),0,(yourformulaehere))
Double the length, but na produced 0, so you can still sum.
You can replace 0 with " ", just to display nothing, but
you cant sum that.
Happy Days

 

[Nick Hodge]

Mike

You should not put a space in the middle of " " as this creates a space. To
create a blank cell which shouldn't mess with calculations use "". (No
space)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)