Simply put, when Excel encounters a value divided by zero, a blank/empty cell, or a value that equals zero, it displays the #Div/0! error. Although we will discuss the #div/0! error with respect to Excel, this article is also applicable to other spreadsheet programs such as Google Sheets, Open Office, etc.
Error Due to Division by Zero.
Enter the following formula in cell B2: This will immediately throw a #Div/0! error, as the denominator in the formula is zero. Look at the image below, and you will notice a divide-by-zero error in cell E9. Here, the formula is: But D9 is 0, so the error.
Error after division by a blank cell.
Enter 10 in cell B2 and input the following formula in cell D3: This will result in a #Div/0! error because cell C2 is blank and treated as zero in Excel calculations.
Error in an Average Formula Due to Non-Numeric Value
Enter the values shown in the image below and input the following formula into cell C15: However, this will result in a #Div/0! error in cell C15. Average is Since the sum and count of numeric values in the given range are both zero (because the values are non-numeric), it results in a 0/0 situation, leading to the div/0! error.
The Averageifs Formula
The statement above is also true for the AVERAGEIF and AVERAGEIFS formulas; however, let us focus on discussing the error at hand with respect to the AVERAGEIFS function. Please refer to the image below: There are two columns: one for color and the other for quantity. We want to calculate an average of the color values based on the data in cells C3:C9. Enter the formula in cell F3. Copy it to cells F4 and F5. You will immediately notice a #DIV/0! error in cell F5. But why? For cell F3, the count of red is 2 (B3 and B5), and the sum is 12 + 8 = 20 (C3 and C5). The average will be 20/2 = 10. For cell F5, the count of black is 0 (since black is not present in the reference range), and the sum is also zero; therefore, it results in a 0/0 calculation, causing a #DIV/0! error.
Error in Another Formula Where Division Is Not Involved.
Enter the following formula in cell E15: But why does it show a #div/0! error? This is a simple sum function, and there is no division involved. The #div/0! error in cell E3 is causing the error in cell E15. MOD is also an Excel formula that can directly cause a #div/0! error.
Reasons for #Div/0! Errors.
So, Excel will display a division by zero error.
Methods for Handling #Div/0! Errors.
As the #Div/0! error has its roots in mathematics and cannot be avoided, first ensure that the error is not caused by a mistake or human error. To do so: If error reporting is enabled, click on the yellow triangle icon and select ‘Show Calculation Steps.’ You can use that to isolate the problem.
How to Find #DIV/0! Error Cells.
One can manually overlook a division by zero error when going through a large data set, and using Excel’s capabilities to find all the cells with #div/0! errors will be of great help. This can also help us decide how to manage these errors.
Use Excel Formulas to Overcome ‘Divided by Zero’ Errors.
There will always be cases where division by zero is inevitable, but you do not want to show a calculation error on your worksheet to your senior, colleague, client, etc. For this purpose, you can trap or mask the #DIV/0! error by using the IF or IFERROR formulas.
Use the IFERROR formula to trap #DIV/0! errors.
IFERROR is an Excel formula used to mask all errors on an Excel sheet. It also functions to trap or mask #Div/0! errors. The syntax of the IFERROR formula is as follows: Here, ‘value’ represents the formula displaying an Excel error (in this case, #DIV/0!), and ‘value_if_error’ is a value that you specify to appear in place of the error. Let’s clarify with a simple example. Enter 10 in cell B2 and input the following formula in cell D2: This will cause a #DIV/0! error in cell D2. Now, enter the following formula in cell D2: You will see that cell D2 is now blank because we have asked the IFERROR formula to display a blank space (denoted by “”) instead of the #DIV/0! error.
Add a custom message to the IFERROR formula.
If you do not want to display a blank cell, you can add a custom message to your formula, which can be helpful in the future when you or someone else revisits the formula. To do so, let’s continue with our simple example: If you want to apply the IFERROR formula to a column or row, simply copy and paste it.
Limitations of the IFERROR Formula.
Here are some limitations of the IFERROR formula:
Use the IF formula to solve #Div/0!
#Div/0! can be solved (not just masked or trapped) by using the logic of the IF formula, but the solution will differ from case to case. The general syntax of the IF formula is as follows: A logical test is a condition that you want to test. If it is true, the first value is returned; otherwise, the second value is returned. To better understand, let us continue with our simple example: The same logic can be applied to other, more complex scenarios as well. There is no limit to the logic that can be used with IF and OR to overcome any #div/0! error, but that logic is dependent on your particular use case.
Remove the #DIV/0! error using ISERROR and IF.
If you are using an older version of Excel (2003 or earlier) or plan to share your workbook with a user of such a version, then IFERROR will not function, and you will need to use IF and ISERROR to mask or trap #div/0! errors. ISERROR is a Boolean function that checks for errors. If an error is present, it displays ‘True’; if not, it shows ‘False.’ Let’s continue with our simple example. A point to remember is that ISERROR will also mask all other errors, not just #div/0! errors, and must be used with caution to avoid incorrect calculations or decisions.
Remove the #DIV/0! error in the PivotTable.
#DIV/0! errors in PivotTables can be handled differently. Moreover, a PivotTable will not display the desired text in place of a #DIV/0! error if the text is the name of a row or column. Look at the image below, and you will notice #DIV/0! errors in cells D6, D7, D9, and F5. To change this: Hopefully, these lines have clarified your understanding of #div/0! errors. If you have any questions, feel free to post them in the comments section.
