5 Minute Tips General Preventing Excel Divide by 0 Error | Preventing Excel Divide by 0 Error |
|
| Wednesday, 16 January 2008 | ||
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 ExcelWithout 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.  Click to enlarge 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 FormulaThere 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.  Click to enlarge 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.)
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.  Click to enlarge 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”. =IF(D2=0,"na",C2/D2) or =IF(D2=0,C2,C2/D2) 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! Related Excel Articles How to use Excel Conditional Formatting
|
||
| Last Updated ( Sunday, 18 May 2008 ) | ||
