In Microsoft Excel or Microsoft Office Excel spreadsheet program, when a value is divided by zero (0) or a blank cell in the formula, the cell that displays the result will show the error value #DIV/0! as the result of the calculation.
The error value may give wrong impression to readers especially when the workbook or worksheet is still in preparation. And it’s not a professional or nice look when the same formula is applied to multiple cells by dragging or copying and all these rows or columns return an “#DIV/0!” error value.
The error value is valid though, as it’s impossible to divide any value with 0. However, it’s possible to suppress, hide or remove the #DIV/0! error value in the cell for unsupported or invalid division formula by using IF worksheet function.
To suppress #DIV/0 error value or keep #DIV/0! from appearing when there is an error, 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.
For example, for formula of “A1/A2” (cell A1 devided by cell A2), the formula to enter into the result cell is as below:
=IF(A2=0,"",A1/A2)
The formula above checks to see if the denominator equals zero (or is blank); if so, a blank cell is displayed. Otherwise, the cell that contains the function will display the result of the formula expression after calculating.
Note: 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.
Another IF worksheet formula that works utilizing the ISERROR condition. The syntax for the formula portion is:
=IF(ISERROR(numerator/denominator),"",numerator/denominator)
For example,
=IF(ISERROR(A1/A2),"",A1/A2)
Again, to display other information on the cell if the calculation result is erroneous, change the value of the middle parameter (text string requires quotation marks).
After entering the error handling formula as above, users can drag to copy the formula to apply to other cells, and the similar formula will be applied automatically.
Recent Posts
- Able2Extract Professional 11 Review – A Powerful PDF Tool
- How to Install Windows 10 & Windows 8.1 with Local Account (Bypass Microsoft Account Sign In)
- How to Upgrade CentOS/Red Hat/Fedora Linux Kernel (cPanel WHM)
- How to Install Popcorn Time Movies & TV Shows Streaming App on iOS (iPhone & iPad) With No Jailbreak
- Stream & Watch Free Torrent Movies & TV Series on iOS with Movie Box (No Jailbreak)
Tip and Trick
- How To Download HBO Shows On iPhone, iPad Through Apple TV App
- Windows 10 Insider Preview Build 19025 (20H1) for PC Official Available for Insiders in Fast Ring – Here’s What’s News, Fixes, and Enhancement Changelog
- Kaspersky Total Security 2020 Free Download With License Serial Key
- Steganos Privacy Suite 19 Free Download With Genuine License Key
- Zemana AntiMalware Premium Free Download For Limited Time