#Div/0! Excel Error

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What is Div/0! Error in Excel?

#DIV/0! is the division error in Excel. If we divide any number by zero, we get this error. So, this is why the error appears as “#DIV/0!”

For example, if we want to find the student grade percentage, we need the total marks for the exam written and the achieved score. So, with these two numbers, we can get 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

John has achieved 85 marks in the above image, but we do not have a total exam score. And we need to calculate the percentage of a student, John.

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

We got this error since we do not have any value in the C2 cell. We get this error if we divide any number by an empty or zero-value cell.

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

Below are examples regarding the #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 Excel divides the number by an empty cell or zero value. But we can get this error in many other situations. Let us look at them in detail now.

#1 – Dividing by Zero

  • Look at the below formula image.
Example 1.4

As we can see above, we have two #DIV/0! Error-values in cells D2 and D5 because in cell C2, we have no value. So, it becomes an empty cell. Cell C5 has zero value, leading 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, 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. We get this error when we try to find the average for blank or empty cells.

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

In this scenario, too, we have got #DIV/0! Error because there is no single numerical value in the formula range from B2 to B6. Since all the values are non-numerical, we got this division error.

Example 3.1
Div0 Error in Excel Example 4

We have the city-wise temperature on two consecutive dates in the first table. We tried to find each city’s average temperature in the second table and applied the AVERAGEIF function.

Example 4.1

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

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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

Now we know in what scenarios we get this error. So let us look at the ways of tackling this error in Excel.

#1 – Use the IFERROR Function

IFERROR in excelIFERROR In ExcelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.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 got the #DIV/0! Error.

#2 – Use the IF Function

We can also use the IF function in ExcelIF Function In ExcelIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. read more 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, return the result as “0” or 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! Because the range of cells supplied to the AVERAGE function has not even a single numerical value.
  • SUM function returns #DIV/0! Error if the supplied SUM function range has at least one #DIV/0! Value in it.

This article is a guide to #Div/0 Excel error. We discuss examples of #Div/0 error in Excel and how to tackle it, examples, and a downloadable Excel template. You may also look at these useful functions in Excel: –