dmw | consultancy
tel 01732 833085
tonbridge · kent · UK

How to Suppress the Excel #DIV/0! Error

Last updated on 2018-08-01.


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. Is there a formula I can use to suppress the error?”


Response

Yes there is. Here is a formula to try in cell C1:

=IF(ISERR(A1/B1),"",A1/B1)

Using "" for the second argument of the IF function cause Excel to return nothing to the cell.

As a bonus of applying this formula and copying it down Column C, you can sum the column content without #DIV/0! appearing as the result.


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 in infinity ... but not in Excel.