DMW logo
tel 01732 833085
tonbridge · kent · UK

How to Deal With the Microsoft® Excel #DIV/0! Error

How to suppress the #DIV/0! error warning in Excel caused by a formula that tries to divide a number by zero or by nothing at all.

Last updated on 2020-05-26.


Question

“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?”


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:

Div by Zero

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, 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:

Excel IFERROR

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) —

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification


Disclaimer

DMW Consultancy Ltd 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.

Dividing 27 by zero results not in infinity according to Excel, but in “#DIV/0!”.