# A to Z of Excel Functions: the IFERROR Function

25 May 2020

*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.*

**=IF(Denominator=0, Error_Trap, Numerator / Denominator)**

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

**=IF(ISERROR(Numerator / Denominator), Error_Trap, Numerator / Denominator)**

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

**=IFERROR(Calculation, Error_Trap)**

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:

**=IF(ISERROR(Calculation), Error_Trap, Different_Calculation)**

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. *