You are currently offline, waiting for your internet to reconnect

How to use the IF worksheet function to suppress the #DIV/0!error value in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q291050
For a Microsoft Excel 2000 version of this article, see 182188.
SUMMARY
When you divide by 0 (zero) or a blank cell, Microsoft Excel displaysthe error value "#DIV/0!" as the result of the calculation. This articleshows 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 thestandard division formula:
   =IF(denominator=0,"",numerator/denominator)				
Numerator refers to the cell to be divided. Denominator refers to thecell that is the divisor.

This formula checks to see if the denominator equals zero (or isblank); if so, it displays a blank cell. For example, if you want todivide cell A1 by cell A2 and put the result in cell A3, the formula incell 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 "" (quotationmarks) 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.
REFERENCES
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
182189 How to create a conditional format to hide errors in Excel
OfficeKBHowTo inf XL2002 mistake divide divided XL2003 XL2007
Properties

Article ID: 291050 - Last Review: 01/31/2007 19:04:05 - Revision: 3.2

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition

  • kbhowto KB291050
Feedback
/html>dy>f?DI=4050&did=1&t=">