A to Z of Excel Functions: The IFERROR Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the IFERROR function.

The IFERROR function

IFERROR first came into being back in Excel 2007.  It was something users had asked Microsoft for, for a very long time.  But let me go back in time first and explain why.

At the time of writing, there are 12 IS functions, i.e. functions that give rise to a TRUE or FALSE value depending upon whether a certain condition is met:

1.      ISBLANK(Reference): checks whether the Reference is to an empty cell

2.      ISERR(Value): checks whether the Value is an error (e.g. #REF!, #DIV/0!, #NULL!).  This check specifically excludes #N/A

3.      ISERROR(Value): checks whether the Value is an error (e.g. #REF!, #DIV/0!, #NULL!).  This is probably the most commonly used of these functions in financial modelling

4.      ISEVEN(Number): checks to see if the Number is even

5.      ISFORMULA(Reference): checks to see whether the Reference is to a cell containing a formula

6.      ISLOGICAL(Value): checks to see whether the Value is a logical (TRUE or FALSE) value

7.      ISNA(Value): checks to see whether the Value is #N/A.  This gives us the rather crude identity ISERR + ISNA = ISERROR

8.      ISNONTEXT(Value):  checks whether the Value is not text (N.B. blank cells are not text)

9.      ISNUMBER(Value): checks whether the Value is a number

10.   ISODD(Number): checks to see if the Number is odd.  Personally, I find the number 46 very odd, but Excel doesn’t

11.   ISREF(Value): checks whether the Value is a reference

12.   ISTEXT(Value): checks whether the Value is text.

You get the idea.  As mentioned previously, sometimes you need to trap errors that may originate from a formula that is correct most of the time.  Where possible, you should be specific with regard to what you are checking, e.g.

In this example, I am checking to see whether the Denominator is zero.  I could use this formula instead:

The difference here is that this will check for anything that may give rise to an error:

Do you see the problem here?  I have to put the same formula in twice.  If that is a long formula, then the calculation becomes doubly long.  This is where IFERROR comes in; it halves the length of the calculation but still achieves the same effect

Essentially, this formula is the bastard lovechild of IF and ISERROR.  It checks to see whether the Calculation will give rise to a prima facie error.  If it does, it will return Error_Trap; otherwise, it will perform the said Calculation, e.g.

You shouldn’t just sprinkle IFERROR throughout your models like your formulae are confetti.  Used unwisely, IFERROR can disguise the fact that your formula isn’t working correctly and that modifications to the logic may be required.  Try to use it sparingly.

Sometimes you have to use IF and ISERROR in combination anyway:

In this example, the formula is checking to see whether a particular Calculation gives rise to an error.  If it does, the Error_Trap will be referenced in the usual way, but if not a Different_Calculation (not the Calculation used for the test) will be computed.

These two methodologies should be mastered.  You will create more robust and flexible models once your error become a thing of the past.  Not just the model – but your own expertise – will become more trusted in your organisation if users never encounter prima facie errors in your model.

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.