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.

**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.
--
** John C **


"PD" wrote:

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.

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

"PD" wrote:

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.

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

"Mike H" wrote:

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

"PD" wrote:

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.

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

"John C" wrote:

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

"Mike H" wrote:

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

"PD" wrote:

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.

or -3 even

"John C" wrote:

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

"Mike H" wrote:

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

"PD" wrote:

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.