When reviewing a financial model 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 a financial models.
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 financial modelling tool to find all the risky formulas, but if you don’t have one you can do a manual search using the FIND tool.
Table of contents
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 financial model 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.
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.
