I think I now understand the difference between an Excel tip and an Excel annoyance. It’s an annoyance if the recipient of your spreadsheet doesn’t know the tip and you spend more time defining the issue than it takes to fix it. Next time, I’ll take the five minutes to fix my Excel formula so it doesn’t display the #DIV/0! divide by zero error message.
Dividing by Zero in Excel
Without getting into a semantics debate, Excel does allow you to divide by zero. It also lets you know you have an error. In the resulting cell, it shows the famous line of #DIV/0!. It’s one of those error messages where the letters and numbers make sense, but you also wonder if your PC is swearing at you.
Although your PC isn’t mad, the message may fluster users. Some look at the alert and see the help text “The formula or function used is dividing by zero or empty cells” as shown below. Others might question the data integrity. Personally, I think it’s an aesthetic issue.
The reason I got this Excel error was that I tried to divide my Cost value in C7 by my Catalog Count in D7. This test ad cost $77.45 and generated 0 catalog requests. A similar error occurs if the Catalog Count cell was blank.
Add Logic to Your Excel Formula
There are several ways to fix this error. The best way would be to produce test ads that converted better, but you may not have control of this item. You do have control of Excel and an easy way to change this message is to use the IF function.
This is a logic function where you can direct Excel to do one action if a condition is TRUE and another action if the condition is FALSE.
In this case, I want Excel to take a different action if I have a Catalog Count of “0”. Otherwise, Excel can continue as normal.
How to Display a Blank Value instead of #DIV/0!
(For illustration purposes, these steps are using Excel 2007. The process is similar in other versions.)
- Create a column for your formula. (e.g. Column E Conv Cost)
- Click the next cell down in that column. (e.g. E2)
- Click Insert Function on the Excel ribbon.
- In the Insert Function dialog, select IF
- Click OK.
- In the Function Arguments dialog, click in the Logical_test field.
- Click the top cell in the column which you’re dividing by. (e.g. D2)
- In the same text field after the cell reference type =0. (The field should show something like D2=0)
- Leave the Value_if_true field blank.
- In the Value_if_false field, enter your formula such as C2/D2
- Click OK.
- Copy the Excel formula down to each cell in the column.
You can see in the example below, in each case where I had a “0” or a blank cell in Column D (Catalog Count), Excel left the Conv Cost value in Column E blank. The blank value in the Excel formula is represented as the double quotes.
In short order, you’ll start entering this function directly in the Excel formula bar rather than using the Insert Function dialog.
Other Substitutions for #DIV/0!
Just as some people don’t like the divide by zero error messages, others might object to blank cell values or the zero. They might prefer something like a text value or even the contents from another cell.
You can substitute text or a cell reference instead of having the cell be blank. For example, I could’ve used “na”.
In this last example, Excel would insert the Cost value in the Conv Cost cell instead. Depending on your situation this may be more accurate. In my example of catalog conversion costs, using the Cost value might be better than “0” or a blank.
Excel allows you to fix this error in several ways. Another popular solution from readers mentioned that the ERROR.TYPE method works well. Microsoft has assigned an error value of “2” for this #DIV/0 example. You can find more details on ERROR.TYPE on the Microsoft site.
As you can see there are several ways you can remove the divide by zero error in Excel. Chances are whichever method you choose will be less annoying for your users than seeing #DIV/0!