If the formulas in your Excel reports and analyses display errors like #DIV/0! or #VALUE!, here are some simple ways to trap them.

5639

It’s virtually guaranteed. If you have a bunch of formulas like this…

M43:   =C43/D43

…then the divisor, D43 in this instance, will have a value of zero at some point. And when it does, your formulas will return: #DIV/0!

A visitor recently sent me a workbook with hundreds of results like this. It also had #VALUE! errors where his formulas were trying to divide a number by a label.

Whenever you set ratios in your workbook, it’s always a good idea to decide how you want divide-by-zero and other such errors displayed. For ratios, where divide-by-zero calculations are common, I like to display an empty cell for such errors. So my formula would be:

M43:   =IF(N(D43)=0,””,C43/D43)

Here, the N() function returns zero if cell D43 contains text, an empty cell, or zero. Otherwise, it returns the number in cell D43.

For more complex formulas, where the potential reason for an error might not be obvious, you could use:

M43:   =IF(ISERROR(formula), error-value, formula)

Years ago, however, I complained about this technique to Microsoft’s Excel team. As I remember, I said it made no sense to use formulas like this:

=IF(ISERROR(some-long-and-complex-formula), error-value, the-same-long-and-complex-formula)

There are two problems with this approach, I wrote. First, it doubles the complexity of our formulas. Second, where the formulas use lookups or other processor-intensive calculations, this technique doubles an already-long calculation time.