Hash # Errors and IFERROR
One of my bugbears is a report that includes a # error, such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!
My eye is drawn to the error and I'm always thinking what other errors are there in the analysis rather than focussing on the information presented (which is probably correct!).
In Excel 2010 onwards the IFERROR function is available to resolve this problem, previously and I still see it today, the IF function is used to evaluate the formula, for example =IF(K8=0,0,K11/K8).
The IFERROR function returns a value you specify if a formula evaluates to an error, otherwise it returns the result of the formula itself.
In the example below cell K12 has a #DIV/0! error as the cell formula is K11/K8 and K8 is zero. However, cell N12 is returning 0% because the IFERROR function has been included as part of the formula.
How to type the formula (Syntax)
=IFERROR(value, value if an error)
Arguments
value - this can be a formula such as a margin percentage calculation.
Often the 'value if an error' is defined as zero so that numbers can be added without error or as in this example the percentage is recorded as zero. This is absolutely fine but there is a cautionary warning in that by defining the 'value if an error' as zero it could mask an error in your formula and if you have complicated formulae in your spreadsheet it may be difficult to pick up a genuine formula error when it is recorded as a zero.
Therefore, I would suggest that whilst your spreadsheet is being built and tested that something more obvious, such as "ERROR", is chosen rather than zero.
Once your spreadsheet has been tested and formulae verified you can always perform a find and replace and change "ERROR" to 0 [zero].
In the example presented I used "ERROR" as the 'value if an error' when testing the GP% formula (see below). Once tested and the formula for calculating the gross profit was confirmed I performed a find and replace and inserted zero.
So by using IFERROR there is no reason to report hash errors in your reports and analysis, thus ensuring that the attention of your audience is not distracted by #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
