Error Handling in Excel Spreadsheets

by Charley Kyd on October 31, 2010

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, 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:

=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.

{ 2 comments }

Steve Roberts November 1, 2010 at 5:43 am

Learn something new everyday! Thanks for drawing attention to the IFERROR formula. The only time I really used ISERROR was always with VLOOKUP. I can’t wait to use IFERROR instead!

evohnave November 1, 2010 at 6:37 am

Great idea for a UDF for personal.xla! Thanks!

{ 1 trackback }

Previous post:

Next post: