#N/A
 

=Max(A1:A20) returns #N/A when one or more of the cells in
the series contains #N/A.

Is there a way to cause the formula to overlook the #N/A
and return the maximum value? Or, is there another
function that would accomplish this?

Paul,

This is an array variation of the formula

=MAX(IF(NOT(ISNA(A1:A20)),A1:A20))

commit it with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul" wrote in message
...
=Max(A1:A20) returns #N/A when one or more of the cells in
the series contains #N/A.

Is there a way to cause the formula to overlook the #N/A
and return the maximum value? Or, is there another
function that would accomplish this?

works perfectly!!

where can i learn about writing array formulas - have no
clue about that realm of excel.


-----Original Message-----
Paul,

This is an array variation of the formula

=MAX(IF(NOT(ISNA(A1:A20)),A1:A20))

commit it with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul" wrote in
message
...
=Max(A1:A20) returns #N/A when one or more of the cells
in
the series contains #N/A.

Is there a way to cause the formula to overlook the #N/A
and return the maximum value? Or, is there another
function that would accomplish this?




.

What I know about them I have picked up from within these NGs, but here are
some links that I found

http://office.microsoft.com/en-us/as...872901033.aspx
Introducing array formulas in Excel

http://office.microsoft.com/en-us/as...872911033.aspx
More arrays: Introducing array constants in Excel.

http://www.cpearson.com/excel/array.htm

http://www.ozgrid.com/Excel/arrays.htm

Should get you started.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul" wrote in message
...
works perfectly!!

where can i learn about writing array formulas - have no
clue about that realm of excel.


-----Original Message-----
Paul,

This is an array variation of the formula

=MAX(IF(NOT(ISNA(A1:A20)),A1:A20))

commit it with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paul" wrote in
message
...
=Max(A1:A20) returns #N/A when one or more of the cells
in
the series contains #N/A.

Is there a way to cause the formula to overlook the #N/A
and return the maximum value? Or, is there another
function that would accomplish this?




.

A bit shorter...

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

which you need to confirm to control+shift+enter instead of just with enter.

Paul wrote:
=Max(A1:A20) returns #N/A when one or more of the cells in
the series contains #N/A.

Is there a way to cause the formula to overlook the #N/A
and return the maximum value? Or, is there another
function that would accomplish this?