# Improved IFERROR Function

• Recently on Oz, a poster had a formula that had a bunch of IS functions wrapped in IFs:

[COLOR="blue"]=IF(ISNUMBER(cond1), cond1, IF(ISNUMBER(cond2), cond2, IF(ISNUMBER(cond3), cond3, default)))[/COLOR]

(There were more than that, but I don't like counting parens).

The IFERROR function (widely dispersed in Personal.xls files, and now standard in Excel 2007) makes this more compact (and faster) by eliminating the duplication of expressions:

[COLOR="blue"]=IFERROR(cond1, IFERROR(cond2, IFERROR(cond3, default)))[/COLOR]

A little mod to the IFERROR function makes it more compact still, and more convenient for those of us with paren dyslexia:

[COLOR="blue"]=IFERROR(cond1, cond2, cond3, default)[/COLOR]

This modified IFERROR function returns the first non-error result in an indefinite list of arguments (or the last error, if all are errors):

Code
1. Function IFERROR(ParamArray ToEvaluate() As Variant) As Variant
2. Dim vEval As Variant
3. For Each vEval In ToEvaluate
4. IFERROR = vEval
5. If Not IsError(vEval) Then Exit Function
6. Next vEval
7. End Function

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Improved IFERROR Function

Thank you, Dave, I was feeling so ... ignored :crying:

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Improved IFERROR Function

Quote from shg

Thank you, Dave, I was feeling so ... ignored :crying:

Woops sorry buddy.

Very cool, well done.