Ignore #N/A in formula

M

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
 
J

Jason Morin

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

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

HTH
Jason
Atlanta, GA
 
S

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))
 
M

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
 
N

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
[email protected]
 
Top