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! error is caused by an attempt to divide by zero. It may also appear in other formulas that display the #DIV/0! error.
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:
- All cells used by a formula contain valid information
- There are no blank cells used to divide other values
- The cells referenced by a formula do not already display a #DIV/0! error
Note: if you try to divide a number by a text value, you will see a #VALUE error not #DIV/0!.
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 D9 because cell C9 is blank:
=SUM(B9/C9) // #DIV/0! because C9 is blank
To check that C9 has a value, and abort the calculation if no value is available you can use IF like this:
=IF(C9="","",B9/C9) // display nothing if C6 is blank
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:
=IFERROR(B9/C9,"") // displays nothing when C6 is empty
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.
As you can see, I used the IFERROR function in this example.