excel - suppressing the #DIV/0! error

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?

answer

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

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

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 of our Technical Tips solutions are applied. Back up your data; test thoroughly before using on live data.