SUMMARY
When you divide by 0 (zero) or a blank cell, Microsoft Excel displays
the error value "#DIV/0!" as the result of the calculation. This article
shows you how to use the IF worksheet function to suppress the #DIV/0!
error value.
back to the top
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.
NOTE: If the denominator is a nonzero value, the division is calculated.
back to the top
REFERENCES
For additional information, click the article number below
to view the article in the Microsoft Knowledge Base:
182189 XL2000: How to Create a Conditional Format to Hide Errors
For more information about the #DIV/0! error value, click
Microsoft Excel Help on the
Help menu, type
What does the error #DIV/0! mean? in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.
back to the top