Max

[PD]

Hi,

I need a formula that calculates the max value only if the cell has a value

0 and also ignores the #NA error.

The file has a column with formula which through up a #NA error if the
lookup field is empty. The Max formula should still calcuate the maximum
value in the column and ignore the celss with the #NA error.

ie, if the cell A3:A6 contain the following values:

A3 1
A4 #NA
A5 2
A6 #NA

Max(a3:A6) will give the result - #NA. I need a formula that will give the
result - 2.

 

[John C]

**array** formula
=MAX(IF(ISNA(A3:A6),0,A3:A6))
CTRL+Shift+Enter to enter an array formula.
Unless you are searching for a VBA solution here.

 

[Mike H]

Hi,

Try

=MAX(IF(ISNUMBER(A3:A6),A3:A6))

This an array which must be entered with CTRL+Shift+Enter and NOT just
enter. If you do it correctly then Excel will put curly brackets around the
formula{}. You can't type these yourself.

Mike

 

[John C]

What if the numbers in the series are -3 and -5?

 

[Mike H]

Then it will select the larger -5. your point being

 

[Mike H]

or -3 even

What if the numbers in the series are -3 and -5?