#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! error is caused by an attempt to divide by zero. It may also appear in other formulas that display the #DIV/0! error.

 

excelguy.co.za

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:

  1. All cells used by a formula contain valid information
  2. There are no blank cells used to divide other values
  3. 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:

=IFERROR(B9/C9,"Hrs Missing")

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.