Search Not Just Numbers

Tuesday 21 October 2014

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

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".

3 comments:

  1. Hi Glen,

    Personally I do not like this kind of error-trapping
    it also masks all other errors
    why not just check if the divisor is zero?
    = if (b1 = 0, "", a1 / b1)

    best,
    Eric

    ReplyDelete
    Replies
    1. Eric

      That's a good point. I think it comes down to the risk involved in the specific scenario, and personal preference.

      Delete
  2. I like it for vlookups best... if the target data doesn't include the string you're looking for you can return 0 where the lookup returns an error and then include the 0 result in formulae... e.g. comparing two TBs. Rather than masking errors, in this circumstance it prevents the basic vlookup indicating errors where there aren't any.

    Adam

    ReplyDelete