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.
“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:
“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:
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.
In Excel 2007, Microsoft introduced the ISERROR function. It’s more straightforward to apply than the IF…ISERROR function combination described above:
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.
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.