How to Display “0” When a Formula or Function Returns an Error?
In Excel, you may want to display “0” instead of an error when a formula results in an error. This article explains how to use a specific function to display “0” whenever an error occurs in a formula.
Method
To display “0” for a formula that may result in an error, use the IFERROR function. This function allows you to specify a value (in this case, “0”) to return if an error occurs, while returning the actual calculation result if no error is present.
Basic Formula Syntax
=IFERROR(formula, 0)
In this syntax, the first argument is the formula to evaluate, and the second argument is the value to return if an error occurs (here, “0”).
Steps
- Prepare a cell (e.g., A2) that contains a formula which might result in an error.
- In the cell where you want to display the result (e.g., B2), enter the following formula.
Example Formula
For example, if A2 contains “100” and A3 contains “0”, you can use the following formula to calculate A2 divided by A3.
=IFERROR(A2 / A3, 0)
Formula Breakdown
- A2 / A3: Divides the value in A2 by A3. If A3 contains “0”, this will result in the “#DIV/0!” error.
- IFERROR(A2 / A3, 0): Returns “0” if an error occurs; otherwise, it returns the result of the calculation.
Example
In the following table, column A contains numeric data, and column B displays the results of calculations using the values in column A. If an error occurs, “0” is displayed in column B.
| A | B | |
|---|---|---|
| 1 | Value | Result |
| 2 | 100 | =IFERROR(A2 / A3, 0) |
| 3 | 0 | |
| 4 | 50 | =IFERROR(A4 / A5, 0) |
| 5 | 10 |
Results
- B2 cell: The calculation of A2 divided by A3 results in the “#DIV/0!” error, but IFERROR replaces the error with 0.
- B4 cell: The calculation of A4 divided by A5 is successful, so the result 5 is displayed.
Conclusion
Using the IFERROR function in Excel is a convenient way to display “0” when an error occurs in a formula. This method is helpful for managing data smoothly while avoiding error messages in your calculations.