Home |
Search |
Today's Posts |
#1
|
|||
|
|||
#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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? . |
#4
|
|||
|
|||
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? . |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|