dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

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

Techniques for managing the #DIV/0! Error that can be the result of certain Excel formulas.

Last updated on 2018-11-12.


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

The formula in cell C3, =IF(ISERR(A2/B2),"",A2/B2), includes ISERR(A2/B2) as the value of the first of the IF function’s arguments. ISERR returns the logical value TRUE, ie TRUE that there is an error, when it cannot evaluate the expression it is working on, eg A2/B2. It returns FALSE, ie no error, when it can evaluate the expression to its own satisfaction.

The IF evaluates ISERR(A2/B2). When it finds this to be TRUE, IF returns "".

When it finds ISERR(A2/B2) to be FALSE, IF goes ahead with the calculation.

The value "" for the second argument of the IF function causes Excel to return an empty string that displays as nothing on screen.


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, ISERROR returns the value assigned to its second argument. If it has no qualms about making the evaluation, then ISERROR simply returns the result of making it.


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 but according to Excel in “#DOV/0!”.