More FORMULA FRIDAY FUN……….We have all been there I am sure, all is going well when you have written your Excel formula, dragged it down your column of data and there it is, one big ugly divide by zero error. #DIV/0!.
Some Excel users do not mind the 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. We have a data extract for a couple of days where we have the number of calls taken by the centre and 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, but 03 to 05 February 2015 has not yet any data entered, so as the forumula is dividing by zero it correctly displays an error.
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.
This instructs Excel to look at the result of the formula, if there is an error then replace it with 0 or what ever you want to repalce it with. Once this IFERROR is in place, we can easily drag the formula down our data set.
Sorted……the formula again will automatically upate once our data is filled in regarding to call centre.