Hello, Excellers. So, welcome back to another Excel blog post in my #Excel Formula Friday 2020 series. Today, we deal with a formula error. Let me show you how to deal with the #DIV/0!, divide by zero error in Excel. We have all been there I am sure, all is going well when you have written your Excel formula. You have dragged it down your column of data and there it is, one big ugly divide by zero formula error.
Some Excel users do not mind the #DIV/0!, divide by zero error. I am not a fan, and whilst I like to be aware of any errors that Excel flags to me, on a presentation dashboard or a report, I think it does benefit from being tidied up somewhat and with a little bit of Excel formula magic.
Let’s take a look at an example below with some call centre data. I have a data extract for a couple of days where the number of calls taken by the centre as well as the number of called dropped. We then have a calculation in the third column of a simple percentage calculation displaying the % of total calls that were dropped.
The formula is simple dividing the number of called dropped divided by the total number of calls taken, as we can see in the example on 01 and 02 February 2015 3% of calls taken in the call centre were classed as dropped.
I have dragged the formula down through to 02 February 2015. However, 03 to 05 February 2015 has not yet any data entered. So, as the formula is dividing by zero it correctly displays a formula error.
Prevent The #DIV/0! Divide By Zero Error.
So, here is how to prevent the error, in Excel 2007 onwards we can use the IFERROR function in our formula.
The syntax of our formula woudl now be
=IFERROR(value, value if error)
if we apply this with our original small simple formula it woud run like this.
=IFERROR(D3/B3,0)
So, this instructs Excel to look at the result of the formula. Formula errors will be replaced with 0. Or a specified alternative. Once this IFERROR is in place, we can easily drag the formula down our data set.
Finally, the issue is now sorted, by simply using IFERROR. The formula will automatically update once the data is populated. The #DIV/0! formula error is gone for good.
Want Free Excel Tips?
Watch The Divide By Zero Error Video?.
https://howtoexcelatexcel.com/formulas/formula-friday-no-match-in-your-vlookup-oh-na/