Ignore errors in range

[Guest]

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)?

 

[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)

 

[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)?

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

 

[Arvi Laanemets]

Hi

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