Excel Tip: Eliminating #DIV/0! and other errors – an update

From the Not Just Numbers blog:

A while back, I wrote a post explaining an approach to eliminating #DIV/0! and other errors in Excel.

Although the information in the original post is still valid, there is a more streamlined approach you can use in more recent versions of Excel (Excel 2007 onwards).

But before we get into that, a quick reminder that Mynda Treacy’s Excel Dashboards course is closing shortly, so get yourself over there if you don’t want to miss out!

Anyway, on with the post…

If you want to read the original post (out of curiosity or to stay compatible with Excel 2003 and earlier), you can do so here,

Where the original post used a combination of IF and ISERROR, the newer approach uses the IFERROR function.

The syntax for the IFERROR function is as follows:

=IFERROR(value,value if error)

The value argument can be any calculation that may result in an error message, and the value if error argument is what you want to replace the error with.

So, say you have a margin percentage calculation that divides Profit in cell B5 by Sales in B2. You might use the following formula formatted as a percentage:

=B5/B2

However if, in some instances, there might be zero sales, then this function will return the #DIV/0! error.

Instead of seeing #DIV/0! in this case, we can choose what should appear. We may prefer to show zero percent, or a dash, or nothing, or a message such as “No Sales”. We can do any of these with the IFERROR function as follows:

=IFERROR(B5/B2,0)

=IFERROR(B5/B2,”-“)

=IFERROR(B5/B2,””)

=IFERROR(B5/B2,”No Sales”)

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.