#Div/0! Excel Error
Errors are part and parcel of every programming language, which applies to Excel as well. #DIV/0! or the division error is a type of error in excel that mostly occurs when the user divides any number by zero.
For instance, if you want to find the percentage grade of your fellow students, you need two values, one is the total marks scored by the students, and the other one is the total score of the examination. Certainly, with these two values, you will find the grade percentage. If mistakenly, you put the divisor of the formula as 0. As a result, Excel will throw a #Div/0 error.
This tutorial will run you through the definition, examples, different situations in which the #Div/0! Error is thrown, methods to Fix “#DIV/0!” error and various other important points.
What is #Div/0! Error?
The #DIV/0! error occurs in Excel whenever in a formula the user tries to divide by zero, or any number equivalent to zero.
Unlike other Excel errors, the #DIV/0! is helpful because it immediately informs the user that something is missing in the formula or that the user has used some unexpected value in the Excel spreadsheet. Although the primary reason for getting #DIV/0! error is because the user attempt to divide by zero, and it may also appear in other formulas that reference cells that display the #DIV/0! error.
For example, Let’s say you want to find the percentage; you require to have two things to calculate the grade percentage. One is the total marks, and the second is the total score of the examination percentage of different students. When you try to divide the marks field with Total, you will obtain the following output:
In the above example, as you can see, Excel has thrown the #DIV/0! Error in. This is because we were trying to divide the marks with 0.
Scenarios of Getting “#DIV/0!” in Excel
Earlier in this tutorial we have covered that the #DIV/0! error occurs when the user divide a value by empty cell or zero value in a formula, but there are many other situations we will get this error, let’s look at them in detail now.
Following are some scenarios when you Excel throws the DIV/0! Error:
#1 – Dividing any value by Zero
As the name suggests, the primary reason is the DIV/0! Error is triggered in Excel is when the user divides a number by zero. Let’s take an example of finding out the percentage of different students. But the value of the total number is zero.
When you try to divide the marks field with Total, you will obtain the following output:
In the above example, as you can see, Excel has thrown the #DIV/0! Error in all the cells because we have divided the number with 0. Dividing any number with zero given infinite output that couldn’t be calculated. Therefore the formula returns #DIV/0!.
#2- #DIV/0! error and blank cells
Blank cells are one of the common reasons to trigger the DIV/0! Error. Let’s take an example where we will find out the percentage of different students. But mistakenly, two fields in the Total column got missed. When you try to divide the marks field with Total, you will obtain the different outputs.
Look at the below formula image:
In the above example, as you can see, Excel has thrown the #DIV/0! Error in 2 cells (D10 and D12) for the same reason. In cells D10 and D12, we have tried to divide the obtained marks with empty cells (null value). By default, Excel evaluates empty cells as zero, and therefore the formula returns #DIV/0!.
#3 – Summing Cells using Excel SUM formula
Another situation of getting the #DIV/0 error as output is when you calculate the sum of numbers using the SUM function with at least one division error of #DIV/0!.
For example, if you want to find the sum of the percentage column using the SUM formula, you will have the following output:
In the above example, the cells D10 and D12 contains the division error of #DIV/0! as their values. Therefore, applying the SUM EXCEL by taking the range of cells from D10 to D15 gives the division error of #DIV/0! as output.
#4 – AVERAGE Function
Sometimes you may receive the #DIV/0! while using the Excel AVERAGE Function. Let’s say if we want to find the average score of students. But all you can see is the marks field is empty. When we apply the AVERAGE Formula and specify the range of cells (where the cells don’t contain any value), you will get the following output:
In cell B16, we have used the AVERAGE function to find the average makers of students. But instead of giving the AVERAGE the value, Excel has thrown the #DIV/0! Error as an output. When we try to find the average for blank or empty cells, we get this error.
Now, let’s take another scenario where we want to fetch the Average of String values.
While applying the AVERAGE function in case too, Excel has thrown the #DIV/0! Error because, in the formula range from B2 to B6, all the values are of string data type and it doesn’t contain any single numerical value. The AVERAGE function only works for numerical data type.
#5 – AVERAGEIF Function
A similar set of division errors occurs with the AVERAGEIF Function in Excel as well. For example, look at the below example.
How to Fix “#DIV/0!” Error in Excel?
In the above section of this tutorial, we have covered the different situations we can get the Division error #DIV/0! in our Excel worksheet. Now let’s look at the various tricks to fix this error in excel:
#1 – Using the IFERROR Function to fix the error
The Excel IFERROR function is specifically designed to tackle any error. Using this function, you can get any alternative result instead of the default #DIV/0! Error.
For example, we know if we find the average of String data types, it will throw an error. Therefore, we have used IFERROR to get an alternative result of “Incorrect data type, please use Numeric values” whenever we get #DIV/0! Error in our Excel worksheet.
Look at the below image.
#2 – Use IF Function
Another way to tackle #DIV/0! Is by using the inbuilt Excel IF Function. Using the IF function you can test whether the denominator cell is empty or zero.
Refer to the below given example:
In the above example, we have used IF to check whether the denominator is equal to zero or not. Suppose the denominator cell is equal to zero. In that case, the formula will return the result as “sorry cannot divide with 0” or else or any other value, the formula will return the result of the division.
Therefore, using any of the above methods, you can easily tackle #DIV/0! Errors.
Things to Remember
- The Excel AVERAGE function returns the #DIV/0! error as an output if the specified input range of cells does not contain even single numerical data.
- The Excel SUM function returns the error #DIV/0! as an output if the range given to the SUM function contains at least one #DIV/0! value in it.
