Although a #DIV/0! error is caused by an attempt to divide by zero, it may also appear in other formulas that reference cells that display the #DIV/0! error. For example, if any cell in A1:A5 contains a #DIV/0! error, the SUM formula below will display #DIV/0!: The best way to prevent #DIV/0! errors is make sure data is complete. If you see an unexpected #DIV/0! error, check the following: Note: if you try to divide a number by a text value, you will see a #VALUE error not #DIV/0!.
#DIV/0! error and blank cells
Blank cells are a common cause of #DIV/0! errors. For example, in the screen below, we are calculating quantity per hour in column D with this formula, copied down:
Because C3 is blank, Excel evaluates the value of C3 as zero, and the formula returns #DIV/0!.
#DIV/0! with average functions
Excel has three functions for calculating averages: AVERAGE, AVERAGEIF, and AVERAGEIFS. All three functions can return a #DIV/0! error when the count of “matching” values is zero. This is because the general formula for calculating averages is =sum/count, and count can sometimes be zero. For example, if you try to average a range of cells that only contains text values, the AVERAGE function will return #DIV/0! because the count of numeric values to average is zero:
Similarly, if you use the AVERAGEIF or AVERAGEIFS function with logical criteria that do not match any data, these functions will return #DIV/0! because the count of matching records is zero. For example, in the screen below, we are using the AVERAGEIFS function to calculate an average quantity for each color with this formula:
where “color” (B3:B8) and “quantity” (C3:C8) are named ranges. Because there is no color “blue” in the data (i.e. the count of “blue” records is zero), AVERAGEIFS returns #DIV/0!. This can be confusing when you are “certain” there are matching records. The best way to troubleshoot is to set up a small sample of hand-entered data to validate the criteria you are using. If you are applying multiple criteria with AVERAGEIFS, work step by step and only add one criteria at a time. Once you get the example working with criteria as expected, move to real data. More information on formula criteria here.
Trapping the #DIV/0! error with IF
A simple way to trap the #DIV/0! is to check required values with the IF function. In the example shown, the #DIV/0! error appears in cell D6 because cell C6 is blank: To check that C6 has a value, and abort the calculation if no value is available you can use IF like this: You can extend this idea further and check that both B6 and C6 have values using the OR function: See also: IF cell is this OR that.
Trapping the #DIV/0! error with IFERROR
Another option for trapping the #DIV/0! error is the IFERROR function. IFERROR will catch any error and return an alternative result. To trap the #DIV/0! error, wrap the IFERROR function around the formula in D6 like this:
Add a message
If you want to display a message when you trap an #DIV/0! error, just wrap the message in quotes. For example, to display the message “Please enter hours”, you can use: This message will be displayed instead of #DIV/0! while C6 remains blank.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
