Finding the IFERROR, ISERROR, ISERR, ISNA, ISREF, IFNA errors

Free Preview

When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding the IFERROR, ISERROR, ISERR, ISNA, ISREF, IFNA errors, in order of riskiness, when auditing an Excel Spreadsheet.

All the above functions, plus the AGGREGATE function, hide errors. It is required as some errors in Excel are acceptable, and to avoid affecting the whole spreadsheet, need to be suppressed. For example a DIV!0 error is OK as mathematically that is the result, but it stops the rest of the spreadsheet from calculating correctly. Similarly a NA in a lookup functions tells you that it cannot find the item, which may be correct and useful to know.

The risk however is that the hidden error was actually a genuine spreadsheet error which has been ignored. In some cases it would be better that your spreadsheet fell over with error messages so that you know there is a problem instead of the errors being hidden and you remain unaware.

It is useful to have a spreadsheet error add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.

Functions Inside the IFERROR and other error hiding functions

In these cases the question is whether the function inside the IFERROR or ISERROR has an ‘acceptable’ error or is it hiding a proper spreadsheet error. In this case you need to assess the inside function to make sure it works as expected. For example if there are lookups inside an IFERROR. are there any of the LOOKUP risks?

As shown below, when we can get a listing of all the IFERROR’s we can see what is inside them. The VLOOKUP seems to have a number of risks ($ signs missing, TRUE used as the logic test) which the IFERROR will hide. In most cases we need to assess whether the errors being suppressed are acceptable or not.

Finding the IFERROR, ISERROR, ISERR, ISNA, ISREF, IFNA errors

What happens if an error is found

When looking at these error suppressions, also consider what Excel has been told to do if it finds an error. For example does it show a 0 or a “-“. This is important as it may affect other functions e.g. SUM or AVERAGE, MIN, MAX.

AGGREGATE error suppression

Also be aware that built into the AGGREGATE function is the ability to ignore error messages when performing an arithmetic calculation. You should just check that genuine errors aren’t being ignored. So below in the AGGREGATE formula in cell E23 we have an answer, but in the range it is looking there is a #NAME? error. We would rather see the NAME error and figure out what it is.

Back to: Auditing an Excel spreadsheet > Prioritise which formula to look in for errors