The Sisyphus Comments

Dividing By Zero In Excel – Problem Solved

If you are in charge of preparing the budget for your agency or administering a grant, you may have to set up or enter data into a Microsoft Exel spreadsheet. If your spreadsheet involves a division formula, you may experience a problem if the you try to divide by a cell containing 0 (zero). When you divide by 0 (zero) or a blank cell, Excel displays the error value “#DIV/0!” as the result of the calculation. You can use the IF worksheet function to suppress the #DIV/0! error value.

Suppress #DIV/0 Error Value

To keep #DIV/0! from appearing, use the following formula in place of the standard division formula:

=IF(denominator=0,””,numerator/denominator)

“Numerator” refers to the cell to be divided. “Denominator” refers to the cell that is the divisor.

This formula checks to see if the denominator equals zero (or is blank); if so, a blank cell is displayed. For example, if you want to divide cell A1 by cell A2 and put the result in cell A3, the formula in cell A3 would be:

$A$3: =IF(A2=0,””,A1/A2)

Cell A3 appears blank if cell A2 is blank or contains a zero. Otherwise, A3 contains the result of the expression A1/A2.

To display other information in the cell if the divisor is blank or zero, type the necessary information in the formula where the quotation marks (“”) appear. If you want to display text, type it between these quotation marks. If you want to display anything else (values), type it instead of the quotation marks. Of course, If the denominator is a nonzero value, the division is calculated.

You probably won’t use this tip everyday but when you run into this problem just visit the The Sisyphus Comments and type ‘Excel” in the search box to find this article. Remember, you can also use the search box to find any article you have read in the blog.