The #DIV/0 error is probably the most simple error to fix in Excel.
There is only one main reason for it, but there are a ton of ways that you could do to remove it.
In this article, I’ll show you the root cause of this error and some examples of solutions that you could do to correct it.
Table of Contents
What causes the #DIV/0 error in Excel?
The #DIV/0 error will only appear when you try to divide a number by zero — it’s that simple.
We all know that a number can’t be divided by zero.
But then you might ask, “My formula does not even have the division operator; why am I still having this error?”
If this is so, it could be because one of the cells referred to in your formula has the #DIV/0 error; thus, you need to remove the error from these cells.
Also, if you are working on the AVERAGE() function, ensure that the ranges specified in your formula have numbers.
If all the cells contain texts (or are left blank), you will have the #DIV/0 error.
If you are working on the AVERAGEIF() or AVERAGEIFS() function, check if your “criteria” is within the specified range of data.
The #DIV/0 error can occur in these functions if the “criteria” does not match any item in the range of data.
Once you’ve identified the cell where you need to make the changes, the next step is to apply the suitable solution.
Fix the #DIV/0 Error
If you are working on a complex formula with more than one Excel function, break it down into small chunks. Try to find where exactly the error occurs.
To help you find it, remember that the #DIV/0 error will likely appear in the following functions:
- Division (÷)
- AVERAGE()
- AVERAGEIF() or AVERAGEIFS()
If you are not using these functions, look into the cells specified in your formula. These cells may contain the #DIV/0 error. If so, then you have to remove the error from there.
1. Division formula with no divisor
If your divisor is a fixed number in the formula and is equal to 0, then the only way to fix the error is to change it to a non-zero number.
If your divisor is referring to a cell that is either blank or equal to zero, you could either:
- Edit the contents of the cell referred to in the formula. Change them into a non-zero number; or
- Edit your current formula so that it displays (or does) something else when the “source cell” is blank or equal to zero.
If you want to display a number or a text whenever an error occurs, you can use either of these formulas:
| =IFERROR(A2/B2, 0) | This formula will replace the error with zero. Use this on ranges that serve as the basis for the totals. |
| =IFERROR(A2/B2, “User Input Required”) | Use this formula to add a text like notifying the user if his inputs are required. |
If you want to have a secondary divisor that you could use if the primary divisor is zero, then you could use the following formula:
=IF([divisor 1]=0, [dividend]/[divisor 2], [dividend]/[divisor 1])
This formula tells Excel to use Divisor 2 if Divisor 1 is equal to zero (or is left blank).
What if Divisor 2 is left blank too? You could configure the formula to display, for example, a text to indicate that all the divisors are missing.
=IF([divisor 1]=0, [dividend]/[divisor 2], IF([divisor 2]=0, “Divisors Missing”, [dividend]/[divisor 1]))
2. Excel formulas with the AVERAGE() function
The #DIV/0 error in AVERAGE() functions will only happen if the cells referred to in the formula are either ALL blank or ALL texts (no numbers).
This error can occur whenever you try to copy cell formulas with no absolute references (no ‘$’ in the ranges).
To fix this, you only need to adjust the formula to point it to the correct cells – those with numbers.
3. Excel formulas with the AVERAGEIF() or AVERAGEIFS() function
The #DIV/0 error in AVERAGEIF() or AVERAGEIFS() can happen if the “criteria” does not match any of the values in the data range that you specified.
There are two ways to fix this:
- Change the “criteria” to match at least one of the values in the selected data set.
- Update the formula so that it displays (or does) something else whenever an error occurs. You can use the IFERROR() function for this.
Conclusion
The #DIV/0 error will only occur if a number has nothing to divide by (divisor = 0). There are, however, different reasons why this can happen.
Remember to check the range of cells specified in your formula. They might have the #DIV/0 error, so you’ll have to fix the issues from there first.
| Learn More About Other Errors |
|---|
| #NAME Error in Excel |
| #NUM Error in Excel |
| #VALUE Error in Excel |