Explanation
About the #DIV/0!
error
The #DIV/0!
error appears when a formula attempts to divide by zero, or a value equivalent to zero.
Like other errors, the #DIV/0!
is useful, because it tells you there is something missing or unexpected in a spreadsheet.
You may see #DIV/0!
errors when data is being entered, but is not yet complete.
For example, a cell in the worksheet is blank because data is not yet available.
Although a #DIV/0!
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 to double-check data is complete.
If you see an unexpected #DIV/0!
#DIV/0!
error and blank cells
Blank cells are a common cause of #DIV/0!
#DIV/0!
with average functions
Excel has three functions for calculating averages:AVERAGE,AVERAGEIF, andAVERAGEIFS.
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.
because the count of matching records is zero.
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.
Trapping the #DIV/0!
error with IF
A simple way to trap the #DIV/0!
is to check required values with theIF function.
In the example shown, the #DIV/0!
Trapping the #DIV/0!
error with IFERROR
Another option for trapping the #DIV/0!
error is theIFERROR function.
IFERROR will catch any error and return an alternative result.
To trap the #DIV/0!
error, just wrap the message in quotes.
while C6 remains blank.
error
How to fix the #REF!
error
How to fix the #NAME?
error
How to fix the #VALUE!
error
How to fix the #NUM!
error
How to fix the #NULL!
error
How to fix the #CALC!
IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.
you’re free to use ERROR.pop in to test for specific kinds of errors.
If no error exists, ERROR.throw in returns #N/A.
See below for a key to the error codes returned by ERROR.punch in.
…
Related videos
Excel formula error codes
How to trace a formula error
