In this tutorial, we will know what is #DIV/0 Error in Excel at the same time why and when do we get this error in our calculations.
Error is a common and necessary part of Excel formulas and functions, but fixing those errors is what distinguishes you as an Excel expert.
Definitely, as a beginner finding those errors and restoring them to work properly again is a little bit hard and tricky. However, every error is the result of the user’s error. The reason why it is significant to understand why this occurs.
What is a #DIV/0! Error in Excel?
The #DIV/0! error occurs when data is divided to zero or an empty cell. This is more likely to happen when the formula is already set up but no data is entered in a cell for the formula reference.
Basically like other errors, #DIV/0! is important since it is a teller of what is missing or unexpected in a spreadsheet.
Fortunately, make sure to complete the data to prevent this error to occur. But if you see some unexpected #DIV/0! error try to investigate the following:
- All cells have valid information before using it in the formula.
- There are no blank or empty cells used to divide other values.
- The cells referenced by the formula do not already have a #DIV/0! error.
How to fix DIV/0 error in Excel
Here are some examples of ways to fix #DIV/0! errors. Along with common reasons why we encounter this #DIV/0! error.
Fix #DIV/0! Error Using IFERROR
Having trouble with #DIV/0! error in working with formula, the IFERROR technique is the best way to get rid of this error.
So here is the syntax of IFERROR function in creating a formula:
=IFERROR(value, value_if_error)
Where the arguments are explained below:
- value — it is the argument which can return division an error.
- value_if_error — this is the value specified that you should get when an error occurs.
Now we will elaborate on this using an example.
Supposedly, we have sample data which is given below, where the division formula is placed in Column C.
As you observed based on the image above all the cases having a divisor of 0 or null get #div/0! error in column B.
To fix this, we will consider using the formula below:
=IFERROR(A2/B2,””)
Apparently, the formula returns the correct value of division if it’s not an error, however, it replaces blank when its result is an error.
Meanwhile, if you want #div/0! error to do meaningful other than blank, you can customize it in the second argument. Along with the formula given below:
=IFERROR(A2/B2,”No Sale”)
Reminders about IFERROR formula:
- Since IFERROR formula is introduced in Excel 2007 and higher, prior versions won’t get this formula.
- IFERROR formula works on error values such as N/A, #DIV/0!, #VALUE!, #REF!, #NAME, #NUM, etc. In case either of the formula you may encounter the same method you can get rid of those errors.
#DIV/0 Error in Excel Average
Generally, Excel has three functions to calculate Average these are Average, AverageIf, and AverageIfs. Apparently, this function can return #DIV/0! error. This is because the calculation of average is =sum/count, which we cannot avoid having zero value during counting.
In the same manner, using averageif or averageifs function along with logical criteria which do not match in any data this will return #DIV/0! error. Still, because the matching data count is zero.
For instance, what we have below is a formula of averageifs function that will calculate the following criteria:
=AVERAGEIFS(A2:A10,B2:B10,E2)
As you can see in the image above the formula returns #DIV/0! error since the criteria do not have matching data and simply the average range A2:A20 consists of text strings wherein the average function ignores this type of data.
In order to fix this we are going to fix the proper argument’s value. So what we have is a branch and income column which we are going to make income column as average range and branch column to be the average criteria.
The best way to troubleshoot is to set up a small sample of hand-entered data to validate the criteria you are using. If you are applying multiple criteria with AVERAGEIFS, work step by step and only add one criterion at a time.
Technically to troubleshoot this kind of error the best way is to have small amount of data to validate it easily. Meanwhile, in applying multiple criteria, it’s effective to work step by step and one criterion at a time.
Conclusion
That’s all we need to know about #DIV/0 error in Excel. So in this tutorial, we covered the possible reason along with possible ways to get rid of this error in our worksheet.
Now that you know what cause of this error it is easy for you now to troubleshoot your calculations when you faced this kind of error.
I hope this tutorial is useful in doing your task. Thank you for reading 🙂
