Dealing with the #DIV/0 Error
The #DIV/0! error occurs in Excele when attempting to divide a number by zero or by a value that is equivalent to zero, such as a blank cell. This error indicator, #DIV/0!, alerts you that you are trying to divide a value by zero.
In the following example, there is a numeric value in cell A1 and a zero in cell B1. When a formula is entered in cell C1, the error occurs.
Error with a Blank Cell
Additionally, if you use a cell reference in the formula and any of the referenced cells contain a #DIV/0! error, the same error will appear in the result.
Error with an Average
When using an average function to calculate the average of numeric values, encountering a #DIV/0! error among those values will result in the same error. This problem persists even with other average functions like AVERAGEA, AVERAGEIF, and AVERAGEIFS.
How to Fix (Divide by Zero) Error
The best way to resolve this error is by using the IFERROR function to create a condition for handling errors within the original formula.
To fix the error:
- Enter “IFERROR” in cell C1, followed by an opening parenthesis.
- Use the division formula in the “value” argument.
- Insert a comma and specify the “value_if_error” as the desired result when encountering #DIV/0! or any other error.
- Finally, close the parentheses and press Enter.
=IFERROR(A1/B1, “There’s an Error”)
This solution ensures a more readable result when attempting to divide a number by zero (#DIV/0!).
Using IF to Fix #DIV/0! Error
Alternatively, you can also use the IF function to test for errors and replace the error value with something else.
In the example above, we used the IF and ISERROR functions to create the condition. The formula first employs the IF function, with ISERROR as the first argument, to check for errors in the calculation. If an error exists, it returns the specified value; otherwise, it divides the value in A1 by the value in B1.
