In Excel, the #DIV/0! error occurs when you try to divide a number with a zero, or you have a value equivalent to a zero, which can be a blank value. That’s the reason why you get the sign of #DVI/0! which is an indication that you trying to divide a value with a zero.
In the following example, you see we have a numeric value in cell A1 and then we have a zero in cell B1. Now when you enter a formula in cell C1. You get this error.
#DIV/0! With a Blank Cell
Apart from that, if you are using a cell reference in the formula and any of the cells already have a #DIV/0! you will get this error in the result again.
#DIV/0! Error with an Average
If you are using an average function to calculate the average for some numeric value, and one of those values is a #DIV/0! Error, you will again get the error in the result. Even with other average functions like AVERAGEA, AVERAGEIF, and AVERAGEIFS, you will face the same problem.
How to Fix #DIV/0! (Divide by Zero) Error
The best way to fix this error is to wrap the original formula with the IFERROR IF function to create a condition if an error occurs.
- First, if all, enter IFERROR in cell C1 and enter starting parentheses.
- After that, in the value argument use the divide formula.
- Now, enter a comma, and in the value_if_error enter the value that you want to get if #DIV/0! Error or any other error.
- In the end, add the closing parentheses and hit enter.
=IFERROR(A1/B1,"There's an Error")This is one of the solutions to make sure have a more readable result when you try to divide a number a zero (#DIV/0!).
Using IF to Fix #DIV/0! Error
But apart from this, you can also use the IF condition to create a test if there is an error and then replace that error value with something else.
In the above example, we have used the IF and ISERROR function to create the condition. In the formula, first, we have used the IF function, and in the first argument of the IF, we have used ISERROR to test if there is an error in the calculation.
And if there’s an error it will return the value that we have specified and if there’s no error it will again divide the value from A1 by the values from cell B1.
Get the Excel File
Frequently Asked Questions
To get rid of the #DIV/0! you need to thoroughly check the formula which you are writing and verify is there’s not division with a zero.
The only way to fix #DIV/0! is to use verify your formula or use the IFERROR/ISERROR function to replace the error with a meaningful value.