Software

Inhibit Excel's #DIV/0! error

Don't let Excel's #DIV/0! error spoil your spreadsheet. If the strategy's sound, inhibit the error.
If an equation or formula attempts to divide a value by 0, Excel displays the #DIV/0! error, but that doesn't mean the formula you're using is wrong. Most likely, the logic is sound enough. In mathematical terms, Excel returns this error when the divisor is blank or 0. As you can see in the spreadsheet below, the formula in questions works fine four out of five times. The formula =D4/B4 in cell E4 returns this error value because cell B4 is 0.

If you're the only one using the spreadsheet, you probably don't need to do a thing; you know what the error means. However, if you share the spreadsheet, you'll probably want to inhibit this error value to avoid confusion or worse -- someone might think you're sloppy or even incompetent. To inhibit the display of this error, you can use the IF() function as follows: 

  1. Select cell E2 because you'll want to inhibit all of the formulas in column E, not just the one that's currently displaying an error value.
  2. Replace the simple equation with the following function: =IF(B2=0,"",D2/B2).
  3. Using the Fill handle, copy the new function to cells E3:E6.

When the value in column B is 0 or the cell is blank, the IF() function returns an empty string -- that's the double quotation marks component -- instead of #DIV/0! error. You can replace the empty string ("") with a more descriptive string, such as "Not applicable," if that makes more sense within the context of your spreadsheet's purpose. Don't let the #DIV/0! error value spoil an otherwise sound spreadsheet. Use the IF() function to suppress the error.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
COFGFOR
COFGFOR

If you use "", rather than zero (0) in the IF() function, and were then to AutoSum column E (in cell E6) it would not reach up to rows 2 and 3. So, even if zero (0) isn't technically right (it's still an error), I've made even greater errors when using AutoSum on a column where I've trapped #DIV/0! errors using a text string ("") rather than zero (0).

njconner1
njconner1

If you are using Excel 2003, a broader function for handling errors is: if(iserror(function),0,function)) Any error generated by your function will be handled and replaced with a 0, or any other value you choose ("NA" is another I use often). This can cause your functions to grow pretty quickly though: if(iserror(vlookup(A2,G:Z,3,false)),0,vlookup(A2,G:Z,3,false)) If you are using Excel 2007+, you have the streamlined IfError function: IfError(function,0) Like the first function, if your formula returns an error it replaces it with another value.

Full Tao-er
Full Tao-er

The cells in Column E in the example are formatted as a percentage. If these were calculated as a straight value and cell E6 was an average of E2:E5, then the replacement value in the IF() can change the result. As a text string (""), the value in E4 is ignored and the sum of E2, E3, and E5 are divided by 3. On the other hand, a zero (0) value would be included and the sum of E2, E3, E4, and E5 would be divided by 4. On a side note, there must be decimals not shown because D3 looks to be off by $1.

ssharkins
ssharkins

These functions are always useful. I didn't go that route because I didn't want to inhibit other possible errors, just the division by 0. I'm not sure what other errors that might mean though :) so thanks for mentioning these.