MAX, MAXA functions

[Dave F]

I have a column of data, E:2:E15000. These data are either numbers,
text, or logical errors.

I want to find the maximum value of the numbers in this column. Both
MAX and MAXA give #VALUE! errors, presumably because of the text and
logical errors in this column.

Is there another function that will return the maximum value?

Dave

 

[Jim Rech]

You could use this formula: =MAX(IFERROR(E2:E15000,0))

It must be "array-entered", that is, by pressing Ctrl-Shift-Enter rather
than just Enter.

 

[Jim Rech]

My suggestion assumes the largest number will be 0 or greater.

 

[T. Valko]

If there might be negatives:

=MAX(IFERROR(E2:E100,""))

Note that this will only work in Excel 2007. For previous versions:

=MAX(IF(ISERROR(E2:E100),"",E2:E100))

=MAX(IF(ISNUMBER(E2:E100),E2:E100))

All formulas are array entered. Array formulas need to be entered using the
key combination of CTRL,SHIFT,ENTER (not just ENTER)