ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week #422 - Better error catching formulas

Author: David Lyford-Tilley

Published: 30 Nov 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week we have a Creator level post in which we’re looking at improved ways of writing error catching formulas, starting with replacing the basic IFERROR function.

What’s wrong with IFERROR?

IFERROR is a helpful function that allows you to provide a backup value or formula to be used if the primary one returns an error. Take for example this year-on-year change calculation, which returns a blank if the prior year value is nil:

Excel screenshot

This works just fine; however the issue is that it over-catches. Any error of any kind will be hidden by this formula, not just dividing by zero. This means that the change formula is less useful for catching real errors. For example, let’s say that a current-year figure pulled through a non-numeric value:

Excel screenshot

Now a reviewer is less likely to catch the mistake because the genuine error has been hidden by a formula that was only intended to tidy up divide-by-zero errors.

The best solution is to design your error-catching formulas only to catch a specific error. In this case, for example, you could use this instead:

Excel screenshot

This formula only uses a blank for the specific issue of a zero denominator, and so the #VALUE! error is still shown for the row with a faulty input. Now the comparisons remain neat and tidy, but also still function as a way of spotting unrelated errors.

For one specific errors- the #N/A! error – there is an equivalent of IFERROR that only works for that one specific issue. The #N/A! error marks when a match can’t be found for a lookup function:

Excel screenshot

Here the IFERROR returns “Not found” for both the value for Stuart, which is not present in the lookup table, and for Ethel – which is present but contains a calculation error. A better alternative is to use IFNA instead:

Excel screenshot

Other kinds of errors

As well as literal Excel error values, error-correcting formulas also come in other forms. For example, here’s a naïve balancing figure formula used to correct small rounding errors from foreign currency conversions in a balance sheet:

Excel screenshot

All well and good so long as the problem is just some sub-decimal rounding, but this formula will “correct” any size of balance sheet issue – so if for example a fat thumb adds £70k to the stock figure by accident:

Excel screenshot

A careless reviewer could see the 0 balance and be tricked into thinking that things are OK, whereas of course something has gone very, very wrong. The better approach is to limit the rounding formula so that it can only operate on a fixed scale:

=IF(ABS(0-SUM(C2:C16))<0.01,0-SUM(C2:C16),0)

This allows the rounding formula only a penny tolerance, and otherwise returns 0 and lets the error come through.

There are plenty of other tricks like this – the key point is to think when you’re making any error catching or correcting formula, and make sure that you are only going to fix that one error, and not hide any other sins.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250