Using IFERROR to capture Formula Errors in Excel

Here is another useful Excel Function that was introduced with Excel 2007 that just, well, ‘makes sense’

If you’re designing spreadsheets with lots of formulas and lots of data, there will come a time where you need to error-trap some values, so as not to show #DIV/0 or #N/A for example.

Whilst there are better ways, without a doubt, what I will frequently see is users using someone like this…

The problem here being: Your formulas might itself be very complex – and this method means you’re writing it twice, making your file twice as hard to maintain, change, fix issues etc. It’s also very system intensive, as you’re calculating the formulas twice every time.

Even in 2003, there were ways to improve on this, by being more selective in how you test for an error – but 2007 bring a much better way of doing it, in the form of IFERROR

The syntax is very simple:

And in a practical example (but a much simpler formula than you might typically have)

So you can see the result is the same as using IF(ISERROR()) approach, but the result is a cleaner, easier to maintain, formula.