XL2000: How to Suppress #DIV/0! Error Value in Division

ID: Q182188

The information in this article applies to:

Microsoft Excel 2000

SUMMARY

When dividing 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.

MORE INFORMATION

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, it displays a blank cell. 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 will appear blank if cell A2 is blank or contains a zero. Otherwise, A3 will contain 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) are between the two commas. If you want to display text, type it between these quotation marks. If you want to display anything else (values), type it in place of the quotation marks.

NOTE: If the denominator is a nonzero value, the division is calculated.

REFERENCES

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.

Additional query words: XL2000

Keywords : kbdta xlformula
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto