Ignore errors in range

H

hmm

The function =MAX(A:A) is giving a #N/A error because some of the cells in
column A are errors.

Is there a way to tell MAX to ignore errors (perhaps by putting A:A into
some sort of filter function)?
 
B

Bob Phillips

=MAX(IF(NOT(ISERROR(A1:A100)),A1:A100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

You also cannot use whole columns in array formulae, it must be explicit.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
L

Lacty

The function =MAX(A:A) is giving a #N/A error because some of the cells in
column A are errors.

Is there a way to tell MAX to ignore errors (perhaps by putting A:A into
some sort of filter function)?
Click to expand...


You could also use this formula:

=MAX( IF( ISERROR(A1:A100), "", A1:A100 ) ) which doesn't use the NOT
function

Again it must be entered as an Array formula Ctrl+Shift+Enter
 
A

Arvi Laanemets

Hi

Or implement an error handling into your formulas in column A, like
=IF(ISERROR(YourFormula,"",YourFormula)
 
Top