Perhaps one of my favourite formulae of all time, the IFERROR formula provides both logical and aesthetic improvements to a worksheet.
In its simplest form, it can transform hideous arithmetic errors such as “#DIV/0!” into simpler and less messy alternatives. Consider the simple calculation of uplift in sales at a fruit stand:
For clarity, we’ve calculated the variance separately to the uplift, or Var%. Since we weren’t selling Lemons in 2014, the #DIV/0 error is thrown – we can’t divide by zero. However, containing this formula inside another formula, IFERROR, solves this problem entirely:
So how do we use the IFERROR formula? Thankfully, it’s one of the most simple formulae available. The syntax is:
=IFERROR(value, value_if_error)
Which in practice becomes:
=IFERROR( [calculation], [what to display if the calculation causes an error])
As can be seen in the second screenshot, for my formula, the calculation was the variance in column D, divided by the 2014 fruit volume in column B. When we type this inside the if error formula, it doesn’t change at all. Following the original formula, we simply type the separator mark, almost universally set to the comma, and then move on to what the formula should do in the event that there’s an error resulting from the calculation.
The [value_if_error] portion can contain anything else – a number as in my case above, a text string contained in quotation marks, or another nested formula. For aesthetic preference, I made it resolve to 0, but we could make the result blank by inserting just “” instead of 0.
Now even I can see this isn’t earth shattering – we might easily achieve this another way. However the simple use of if error can also assist in more complex logical problems. Imagine we’re now extracting the fruit sales from a pivot table using the GETPIVOTDATA formula, and there’s still no data for 2014. Using GETPIVOTDATA when there’s no data in the pivot table will throw a #REF! error:
Calculating a sum or other formula on a range including new error will just pass the error along to another cell. However, wrapping our GETPIVOTDATA formula in an IFERROR means that both formulae resolve as we would expect:
Anticipating the comment that we might not want to hide the errors until we know the formulae are otherwise OK, or don’t want to wrap this around every formula we write, wouldn’t it be ideal if we could have a shortcut to wrap only the formulae we need to, when we’re finished writing them?
Of course it would – which is why we here at excel teacher.com are giving you the code to do just that with VBA. Just click here to find this and a number of other automatic solutions in our code library.