ICAEW.com works better with JavaScript enabled.
Exclusive

Get your errors right

Author: Simon Hurst

Published: 24 Jan 2024

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

It can be very important to predict errors that might occur in your spreadsheet and then to build in mechanisms for highlighting them, or dealing with them automatically. Knowing the best functions to use can not only simplify your formulas, but also help to avoid your error handling making the initial problem even worse.

Error checking functions

Sometimes, when creating a workbook, you will want an Excel formula to return a particular result, rather than the default error message, should the formula return an error. A common example would be the calculation of a variance when the divisor is zero, so that the formula would return a #DIV/0! Error. In this case, it would be possible to use a simple IF() function to handle the error and return a chosen value, or a suitable text warning:

=IF(B2=0,0,A2/B2)

In this case, we can easily predict and identify the situation that we want to allow for, and create a formula that will avoid using the formula that causes the error in the first place. However, sometimes we will need our formula to be evaluated before we know that there will be an error. In these cases, we can use one of Excel’s error checking functions to check the value that our formula will return. We will work through the possible functions from the oldest to the most recent.

ISERROR(), ISNA(), ISERR()

The range of IS… functions allows you to see whether the result of a formula satisfies a particular criterion such as being an even number - ISEVEN(), or an odd number – ISODD(). The functions return the Boolean values TRUE or FALSE depending on whether the criterion is met or not.

Three of the IS… functions relate to errors. ISERROR() returns TRUE if the formula evaluates to any error; ISNA() returns TRUE if the formula evaluates to an #N/A error and ISERR() returns TRUE if the formula evaluates to any error other than #N/A:

excel image
Because these functions return TRUE or FALSE, we can use them as the first argument of an IF() function to return a particular value or text string when an error occurs:
excel image

In these three formulas, ISERROR() and ISNA() both return TRUE as the formula returns is an #N/A error, so the IF() functions return the second, value if true, argument. ISERR() doesn’t return an error for #N/A errors, so the third, value if false, argument is returned which is our formula that returns the #N/A error.

The danger of using ISERROR() rather than the more specific ISNA() function is that ISERROR() will return TRUE for any error, so if there was a valid match, but there was some other error, such as a spelling error in the function name, the formula would still evaluate as TRUE and return the “No match” text:

excel image
As you can see, only the ISNA() function allows the actual error to be correctly identified. The other two functions display ‘No match’ even though there is a match. The situation would probably be even more serious if we decided that the failure to return a match should simply return 0. ISERROR() and ISERR() would just ‘silently’ return the wrong answer if the actual error were to be anything other than an #N/A error:
excel image

Hopefully, this shows how important it is to choose the correct, and usually most specific, error check function.

ERROR.TYPE()

It is possible to be even more specific than just choosing ISNA() rather than ISERROR(). The ERROR.TYPE() function returns a number that represents the different error types possible:

excel image

We could use ERROR.TYPE() to provide very specific warning messages based on these return values:

=IF(ERROR.TYPE(E7)=5,"Please check the spelling in the formula","Other error")
IFERROR(), IFNA()

Using the IF() function with the IS… functions requires the formula to be entered twice: once to see if it generates an error and then to return the actual result if it doesn’t. More recent versions of Excel include two additional functions that simplify the formula. Both IFERROR() and IFNA() take two arguments: the formula to be evaluated and the value to return if it evaluates to the chosen error. If it doesn’t evaluate to the error, the function will return the result of the formula without it needing to be added a second time:

excel image

Again, we can see the importance of using the more specific IFNA() version of the function.

XLOOKUP()

We have used VLOOKUP() for our examples of the failure to find a match. In recent versions of Excel, VLOOKUP() has been replaced by the much-enhanced XLOOKUP() function. One of the many benefits of the use of XLOOKUP() compared to the functions that it has replaced is its inclusion of a specific argument to deal with the failure to find a match:

excel image

Conclusion

You can explore many of the various techniques we have covered here, and a great deal more, in the ICAEW archive

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