What is #DIV0! Error in Google Sheets?
The #DIV/0! error in Google Sheets means that a formula with division is attempting to divide a number by zero. It is mathematically impossible; Hence, you get an error message. Therefore, a #DIV/0! Error means that we have a formula where we try to divide with a cell that contains a zero or is blank.
Mathematically, we cannot divide any number by 0, which returns an error. There are many ways to overcome this error, which will be discussed in the upcoming sessions. In the example below, you can see how a #DIV/0! Error occurs when you divide by zero.
Key Takeaways
- The IFS Google Sheets function evaluates multiple conditions and returns a value based on the first true condition.
- The syntax of the IFS Google Sheets function is as follows
=IFS(condition1, value1, [condition2, value2, …])
condition1, condition2…: The logical expression to evaluate.
value1, value2….: The result if the particular condition is TRUE.
- By using IFS, we can avoid the complexity of nested IF statements. It is also clear and concise to understand.
- If none of the conditions are met, you get the #N/A! error as IFS can’t handle errors on its own.
What Causes #DIV0! Error in Google Sheets?
A “#DIV/0!” error occurs when a formula attempts to divide a number by zero. It means that the denominator used in your formula is a zero or a blank cell, resulting in something that is mathematically undefined.
The main causes of a “#DIV/0!” error include:
Direct division by zero: You will get the error if a formula is written directly with a zero or a cell reference containing zero. As seen below, we have 10/0 and 12/A2, where A2 contains zero.
You can see the DIV/0 error.
Data entry errors: If there is an error in data entry in the denominator of a formula, you’ll see the #DIV/0! Error.
Non-numeric denominator: If the denominator is non-numeric or a text value (e.g., =A1/B1 where B1 contains text), it can cause the same error.
How to Remove #DIV0! Error in Google Sheets?
Now that we have seen the different ways in which this error occurs, it is time to see how to remove it. Below are some of the best solutions.
Example #1 – Using IFERROR to Fix the Error
One of the most effective ways to remove the #DIV/0! error is to use the IFERROR function.
The IFERROR function in Google Sheets catches and handles errors in a formula. IFERROR helps you specify an alternative value instead of displaying the error.
The syntax of the function is as follows:
=IFERROR(value, [value_if_error])
Let’s use this formula as a simple example. We have some values in two columns, and we have to divide them by each other.
Step 1: Enter the details as shown below.
Step 2: Enter the regular division formula in cell C1 and press Enter.
=A1/B1. You can see results in a #DIV/0! error.
Step 3: To avoid this, wrap it with IFERROR as follows. Enter it again in C1. Press Enter.
=IFERROR(A1/B1, “Not applicable”)
Step 4: Drag the formula for all the values. If the division results in an error, “Not applicable” will be displayed. This is a quick fix to keep your data professional.
Example #2 – Checking for Zero Divisors with IF
Another useful method to eliminate the #DIV0! Error in Google Sheets is with the IF function. It checks if the divisor is zero. Only after this does it perform the division. This method prevents the error from occurring in the first place, making the data error-free.
Let’s examine this with an example. We use the same data as in Example 1. Enter the data in a sheet
Step 1: Enter the following function in cell C2.
=IF(B1=0, “Not Applicable”, A1/B1)
This formula first checks if the divisor in B1 is zero. If so, we get the output “Not Applicable,” and the division is not done. If B1 is not zero, you get the result of the division. It is particularly useful when you want to display the errors and check for them in the beginning.
Step 2: Drag the formula for all the rows and check the result.
Example #3 – Using Conditional Formatting to Highlight Errors
There may be situations where we would be dealing with huge datasets. In such cases, dealing with the #DIV/0! errors can be difficult to find and mark. It is where conditional formatting helps you! Conditional formatting helps you to automatically format cells based on conditions you specify. We can use conditional formatting to help automatically highlight cells with errors. It would ensure that they are easy to identify and fix.
Let us look at an example. We have the marks of some students in Columns B and C. We find the average by counting the number of subjects they wrote and dividing by the count.
To apply conditional formatting for errors:
Step 1: First, we try to calculate the student’s average in Column D. Enter the following formula in cell D1.
=(B1+C1)/COUNT(B1,C1). Press Enter.
Explanation:
- B1+C1 = adds the two subject marks.
- COUNT(B1,C1) = finds how many subjects the student has written. If blank, it does not count it.
Step 2: Drag the formula to cell D11. See the image below.
Step 3: Click on Format > Conditional formatting. It is to apply conditional formatting.
Step 4: Select the range as Column D.
In the Conditional format rules panel, choose Custom Formula is from the dropdown menu.
Enter the formula:
=ISERROR(D1)
Choose a formatting style that is a particular color to highlight errors. Press Done.
Observe how the cells with #DIV/0! Have been marked. It will highlight cells where the DIV/0 error occurs, helping you identify them.
Example #4 – Handling Errors with Custom Messages
Sometimes, we must provide better context for an error instead of just highlighting it or using IF. This will make the error clearer to the user. We can do this by using custom messages that can inform users about the error.
We use the same dataset as in Example 3 involving the students and their marks.
Step 1: To understand the error better with a detailed message, enter the following function in cell D1.
=IFERROR(A1/B1, “Error: Cannot divide by zero”). Press Enter.
Drag the formula for all the rows and check the result.
This is a very useful method if multiple users use the sheet for different purposes. It helps them understand the error better, which helps them fix it quickly!
Important Things to Note
- By applying conditional formatting to highlight cells that contain the #DIV/0! error, we can quickly and efficiently spot this error in the data.
- The #DIV/0! error can affect many aggregate functions, such as SUM and AVERAGE. We should ensure error handling is implemented when referencing ranges that may have division operations.
- Division by zero in financial data may be due to an invalid or missing value. This error should be properly handled to avoid discrepancies in financial reports.
- Excessive use of error-handling functions like IFERROR in Google Sheets can slow down large spreadsheets, so it’s best to use them strategically.
Frequently Asked Questions (FAQs)
To avoid the error, check for the following points given below.
1. Use the IF function to check if the denominator is zero before performing the division operation.
a. For example, =IF(C1 = 0, “DIvision by zero not possible”, A1/C1)
2. We also use the IFERROR function which is the most common method to handle this issue. It can return a custom message if #DIV/0! occurs.
a. Example: =IFERROR(A1/B1, “Divide by zero error”).
There are many ways you can handle this error. They are as follows.
1. Use the IFERROR or IF function to spot the error and handle it with a custom message.
2. You can highlight the error with conditional formatting that helps you spot them easily in large datasets for handling.
3. Before applying any formula, make sure that you don’t have zeros or blank cells in the denominator.
To highlight cells with the #DIV/0! Error using conditional formatting, follow these simple steps.
1. Select the range of cells which you want to check.
2. Go to Format > Conditional formatting.
3. Go to the option “Custom formula is.”
4. Enter the formula =ISERROR(A1) if your data starts at A1.
5. You can set any formatting concerning the background color.
It will highlight any cells containing errors, such as #DIV/0!
Download Template
This article must help understand #DIV0! Error in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is #DIV0! Error in Google Sheets. We learn what it is and its causes & how to remove it in different ways with examples. You can learn more from the following articles. –
Leave a Reply