How to Correct the #DIV/0! Error in Excel Formulas

Last updated on 2024-05-02.

A Report of the Error

“I have numbers in Columns A and B of an Excel worksheet. In column C I have a formula that divides the number in Column A by the number in Column B. This works OK provided the number in Column B is not zero or missing. When it is, I get the #DIV/0! error:

Excel #DIV/0!

“Is there a formula I can use to suppress the error?”

What's happened here is that the #DIV/0! error warning is caused by a formula trying to divide a number by zero or by nothing at all.

IF and ISERROR Solution

There are a number of formulas you could use to cope with the #DIV/0! error. First, I suggest the use of Excel's IF and ISERROR functions:

Processing Excel Div by Zero error

Examining the formula in cell C3:

=IF(ISERR(A2/B2),"",A2/B2)

It includes ISERR(A2/B2) as the first of the IF function's arguments. ISERR returns the logical value TRUE when it cannot evaluate the expression with which it is assigned — that is, it's true that there is an error.

ISERR returns FALSE — that is, no error — when it can evaluate the expression to Excel's satisfaction.

The IF function has this construction:

IF(condition_test,value_if_true,value_if_false)

In our formula, the IF evaluates the condition test ISERR(A2/B2). When it finds the evaluation to be TRUE, IF returns "" (two double-quotation marks) that represent an empty or zero-length string, which displays as nothing on screen.

When IF finds ISERR(A2/B2) to be FALSE — i.e. no error — IF goes ahead with the calculation.

IFERROR Solution

In Excel 2007, Microsoft introduced the ISERROR function. It's more straightforward to apply than the IF/ISERROR function combination described above:

Use of Excel IFERROR function

When it fails to evaluate the expression you given it as its first argument, IFERROR returns the value assigned to its second argument. If it has no qualms about making the evaluation, then IFERROR simply returns the result of making it.

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

Thanks, in anticipation.

Disclaimer

David Wallis does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.