If(iserror

[StrideExcel]

Just curious what the difference is between the following formulas:

if(iserror(
iferror(
if(isna(

they seem to return the same result


Thanks

 

[schielrn]

The first 2 will return the same results, but you only need to use the formula you are checking once. Like:

=if(iserror(vlookup(A1,B:D,3,0)),0,vlookup(A1,B:D,3,0))

but with iferror it is much shorter:

=iferror(vlookup(A1,B:D,3,0),0)

So I would assume the latter would calculate quicker, but is only available in XL2007.

=if(isna( is like the first one, but will only check for a #N/A error and will not work for #VALUE, #NUM, #DIV/0 and #NAME?.

Hope that helps.

 

[mgirvin]

Dear StrideExcel,

ISERR checks for: #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL
ISERROR checks for: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL
ISNA checks for: #N/A

And as schielrn said, IFERROR is a great new function in Excel 2007 that is shorter to type and calculates much faster in some large formulas!

 

[jbeaucaire]

IFERROR can be added to earlier versions of Excel with a UDF, if you're interested or in need.

Function IFERROR(ToEvaluate As Variant, Default As Variant) As Variant
    If IsArray(ToEvaluate) Then
        IFERROR = IIf(IsError(ToEvaluate(1)), Default, ToEvaluate)
    Else
        IFERROR = IIf(IsError(ToEvaluate), Default, ToEvaluate)
    End If
End Function