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.
Instead, I asked, couldn’t Microsoft give us a function like this:
=FUNCTION(some-long-and-complex-formula, value-if-error)
Some years later, during the Excel 2007 beta, one of the Excel team asked if I had noticed the IFERROR function, which they had added because of my suggestion. So, in New Excel (Excel 2007 and above), you now can use a formula like this:
M43: =IFERROR(C43/D43,””)
If you use New Excel and you don’t use IFERROR in your spreadsheets, it’s time to do so.
