• Skip to main content
  • Skip to primary sidebar

Nerd Excel

Excel Simplified

  • Excel
  • Google Sheets
  • Word
  • Google Docs

How to Fix #DIV/0! Error in Google Sheets

By: Salman Patwegar

A common error in Google Sheets is #DIV/0!. In this article we will be taking a look at what this error is, what causes the error and how you can fix it.

Fix #DIV/0! Error in Google Sheets

What is the #DIV/0! Error?

The #DIV/0! error is caused when your formula divides by zero or a value equivalent to zero. The error can occur even if you are not directly dividing by zero.

For example if you are averaging a bunch of invalid data ranges (like names) then you will see the error.
Similarly, if you are averaging a column that has blank values you will see the error, because the denominator is equivalent to zero.

The error also occurs if any of the cells in your data range contains the error. For example, if any of the cells between A1 and A6 have the #DIV/0! error then running the following formula will also give the same error.

= SUM(A1:A6)

Fix #DIV/0! Error in Google Sheets

There are 5 recommended ways to fix #DIV/0! in Google Sheets, depending on the formula that threw the error.

Check for Blank Cells

The most common reason the #DIV/0! error occurs is because of an undetected blank cell.

In the example below the error is thrown because cell B4 (the denominator) is blank.

Blank Cell Causing Error in Google Sheets

FIX: You need to populate the blank cell and enter in a value that is not zero.

Check for Invalid Data (Usually when using Average Formula)

The AVERAGE formula is known to commonly cause the #DIV/0! error in Google Sheets because of invalid data.

In the example below we are trying to calculate the average of column A, however column A only contains names and names cannot have an average thus an error is thrown.

#DIV/0! Error From Average Formula

A sneaky way this error happens is when using the AVERAGEIF or AVERAGEIFS formula.

In the example below we are trying to calculate the average quote from each company, and we get an error for Mark’s Company.

#DIV/0! Error From AverageIfs Formula

The reason this error is thrown is because we did not get a single quote from Mark’s Company and when trying to get the average the formula encounters a bunch of blank cells.

A mistake like this can be very common on big datasets. If we were getting quotes from hundreds of companies, then it is easy to wrongly assume we had a specific company in our dataset, when we actually did not.

Thus, it is very important to check if your formula is referring to any invalid data.

FIX: For this example, if we used Ctrl + F we would have quickly found out that we did not get a quote from Mark’s Company.

Check if Denominator Evaluates to Zero

A lot of times users think they are not dividing by zero when in fact their denominator somehow does evaluate to zero.

In the example below we are dividing 100 by the sum of the numbers in columns A1 to A6. The #DIV/0! error is thrown because the sum of A1:A6 is zero.

#DIV/0! Error From Zero Denominator

Here is a quick way to figure out if your denominator evaluates to zero:

FIX: Highlight the denominator formula and you will see at the top what it equals to. (See image below)

Check Denominator Value in Google Sheets

Check if Formula Range Contains #DIV/0! Error

This is very common if you are working with a big dataset. As you saw in the blank cells example earlier column D4 contains the #DIV/0! error.

Now if we were to average all the values in column D, then we would also get the #DIV/0! error because our formula range (D2:D4) contains the error as well.

#DIV/0! Error Due to an Existing #DIV/0! Error

Even if we change the formula to the SUM of column D instead of average, we will still get the #DIV/0! error simply because the error exists in our formula range.

FIX: You need to look through your dataset and see if the error exists somewhere else. You can quickly do this by using Ctrl + F and searching for #DIV/0!

Use IFERROR

An option to hide the error is to surround your formula with IFERROR as seen in the example below.

=IFERROR(yourformula)

Surround Formula with IFERROR

Simply replace yourformula with the original formula you had. So, if your formula was originally B4/C4, then you would enter in =IFERROR(B4/C4).

What surrounding your formula with IFERROR does is that if the formula throws an error then it will simply not display anything.

By using the formula below you can even customize what to write if the formula throws an error.

=IFERROR(yourformula, “Custom Message”)

IFERROR Formula With Message in Google Sheets

Just replace yourformula with the original formula and Custom Message with whatever message you want displayed when an error occurs.

Related
  1. How to Fix Circular Dependency Detected Error in Google Sheets
  2. How to Fix Filter Has Mismatched Range Sizes Error in Google Sheets

Primary Sidebar

Popular Now

  • How to Calculate Age in Google Sheets
  • How to Sort by Date in Google Sheets
  • How to Generate Random Numbers in Google Sheets
  • How to Generate Random Numbers in Excel
  • How to Unprotect Excel Sheet Without Password

About | Privacy Policy | Disclosure | Terms | Contact
© Copyright 2022 @Nerdexcel.com · All Rights Reserved