It’s virtually guaranteed. If you have a bunch of formulas like this…
…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:
Here, the N() function returns zero if cell D43 contains text, and it returns the number if cell D43 contains a number.
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:
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:
If you use New Excel and you don’t use IFERROR in your spreadsheets, it’s time to do so.