From the Not Just Numbers blog:
Excel is great for being able to apply the same calculation consistently by simply copying the formula to all of the relevant cells, however often there are times where some of the data isn’t how we would like it and the formula throws up an error.
The most common of these is the #DIV/0! error that Excel returns when trying to divide a number by zero. As Excel struggles with the concept of infinity it returns this error. A common situation where this occurs is in a Gross Margin Percentage calculation. We may have multiple products with sales and profit figures and a formula to calculate the margin as a percentage, i.e. profit/sales formatted as a percentage.
This will work fine until you come to a product with no sales where the formula will return #DIV/0!.
This can be addressed using a combination of the IF statement and ISERROR (note that in Excel 2007 and 2010 there is a combined function called IFERROR, however the solution proposed here will also work in earlier versions of Excel). If you need a refresher on how the IF statement works take a look at :
If cell A1 contains the Sales figure and B1, the Profit, then to show the Margin Percentage in C1, we would format it as a percentage and use the following formula:
=B1/A1
This will return the #DIV/0! if cell A1 is empty or zero.
The ISERROR function returns TRUE if its argument returns an error and FALSE if not, therefore
=ISERROR(B1/A1)
will return TRUE when this #DIV/0! would occur.
We also need to decide what we want to appear instead of the error. In this scenario, I usually show 0% which is what I have assumed for the example.
Using the ISERROR formula above as the condition for the IF statement, we can return zero if it is an error, or the original calculation otherwise. So C1 becomes:
=IF(ISERROR(B1/A1),0,B1/A1)
No more #DIV/0! errors!
A similar approach can be used for any other common errors, such as when a looked up value is not in the list.
If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.