#Div/0! Excel Error

What is Div/0! Error in Excel?

#DIV/0! is the division error in excel, and the reason why it occurs because when we divide any number by zero, we get this error, so this is the reason error appears as #DIV/0!.

For example, when you want to find the student grade percentage, you need to have two things, one is what was the total marks for the exam written and what is the achieved score in the examination, so with these two numbers, we can find the grade percentage.

Div0 Error in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: #Div/0! Excel Error (wallstreetmojo.com)

  • But look at the below scenario.
Div0 Error in Excel Example 1

In the above image, John has achieved 85 marks, but we don’t have a total exam score, and we need to calculate the percentage of student John.

  • To arrive percentage, we need to divide the achieved score by exam score, so apply the formula as B2 / C2.
Example 1.1
  • We have got the division error as #DIV/0!.
Div0 Error in Excel Example 1.3

Since we do not have any value in the C2 cell, we got this error because if you divide any number by empty cell or zero value cell, we get this error.

Scenarios of Getting “#DIV/0!” in Excel

Below are examples regarding #Div/0 error in excel.

You can download this Div0 Error Excel Template here – Div0 Error Excel Template

As we have seen above, #DIV/0! The error occurs when we divide the number by empty cell or zero value in excel, but there are many other situations we will get this error, let’s look at them in detail now.

#1 – Dividing by Zero

  • Look at the below formula image.
Example 1.4

As you can see above, we have two #DIV/0! Error-values in cells D2 & D5 because in cell C2, we have not value, so becomes an empty cell, and the C5 cell has zero value so leads to #DIV/0! Error.

#2 – Summing Cells

  • Look at the below image.
Div0 Error in Excel Example 2

B7 cells have applied the SUM excel formula by taking the range of cells from B2 to B6.

Example 2.1

But we have got a #DIV/0! Error. This error is because, in the range of cells from B2 to B6, we have at least one division error of #DIV/0! In cell B4, so the eventual result of the SUM function is the same.

#3 – AVERAGE Function

  • Look at the below formula image.
Div0 Error in Excel Example 5.3

In cell B7, we have applied the AVERAGE functionAVERAGE FunctionThe AVERAGE function in Excel gives the arithmetic mean of the supplied set of numeric values. This formula is categorized as a Statistical Function. The average formula is =AVERAGE(read more to find the average score of students.

Example 5.2

But the result is #DIV/0! Error. When we try to find the average for blank or empty cells, we get this error.

  • Now, look at the below example.
Div0 Error in Excel Example 3

In this scenario, too, we have got #DIV/0! Error because, in the formula range from B2 to B6, there isn’t any single numerical value since all the values are non-numerical; we got this division error.

Example 3.1
Div0 Error in Excel Example 4

In the first table, we have the city-wise temperature on two consecutive dates. On the second table, we are trying to find the average temperature of each city and applied the AVERAGEIF function.

Example 4.1

But for the city “Surat” we have got #DIV/0! Error because in the original table, there is not city name “Surat,” so when we try to find the average for the city which doesn’t exist in the actual table, we get this error.

How to Fix “#DIV/0!” Error in Excel?

Now we know in what are the scenarios we get this error, now let’s look at the ways of tackling this error in excel.

#1 – Use the IFERROR Function

IFERROR in excelIFERROR In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula.read more is the function specifically used to tackle any error, instead of getting #DIV/0! Error, we can get the alternative result.

  • Look at the below image.
Example 5

In the above function, we have used IFERROR to get the alternative result of “Denominator is Empty or Zero” whenever we get the error of #DIV/0!.

#2 – Use IF Function

We can also use the IF function in excel to test whether the denominator cell is empty or zero.

  • Look at the below scenario.
Div0 Error in Excel Example 5.1

The above formula says if the denominator cell is equal to zero, then return the result as “0” or else return the result of the division. Like this, we can tackle #DIV/0! Errors.

Things to Remember Here

  • The AVERAGE function returns the error of #DIV/0! When the range of cells supplied to the AVERAGE function has not even single numerical values.
  • SUM function returns #DIV/0! Error if the range of supplied to SUM function has at least one #DIV/0! Value in it.

Recommended Articles

This has been a guide to excel #div/0. Here we discuss examples of #div/0 error and how to tackle this error in excel along with examples and a downloadable excel template. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>