IFERROR is a worksheet function that was introduced in Excel 2007. It’s a catch-all error handler that you can wrap around a formula and return a different value if the formula evaluates to an error. Like most people, I was happy to see it to avoid the old

=IF(ISNA(VLOOKUP(...)),"Not Found",VLOOKUP(...))

construct. Two VLOOKUPs in the same formula is terribly inefficient, so this new worksheet function is welcome. Or is it. I read this over at StackOverflow.

It nearly makes me weep. I can think of no better way to get incorrect results than by wrapping every formula in IFERROR. It got me thinking about IFERROR in general, so I went and read about at office.microsoft.com. Here are the examples they give.

More weeping. Boy, I’m emotional today. It’s just an illustration, I know, but consider this

Microsoft’s method is on the left and mine is on the right. My formula is =IF(E3<>0,D3/E3,0). Microsoft’s example totally masks the fact that there’s an error in the first line. They’re trying to avoid a divide by zero error, but they’re using a sledge hammer on a push pin. If they want to catch more errors than just #DIV/0!, they should test for them explicitly.

As long as I’m writing about errors, another error handling construct that I dislike is this one.

=IF(ISERROR(VLOOKUP(...)),"Not Found",VLOOKUP(...))

I don’t like this because ISERROR will detect any kind of error when the only one that should be detected is NA. If there’s a #NUM! error in there, you’ll get the result “Not Found”, which is technically true but hardly helpful and sometimes damaging.

I’d like to have some guidelines about error checking in formulas. I’m the wrong guy to write these guidelines, but I’ll get the ball rolling.

1. Don’t use error handling without a specific reason
2. Test for the condition that would cause the error instead of error handling (examples: zero denominators, all IRR arguments with the same sign)
3. Wrap the smallest piece of a formula with error handling that is possible
4. Use the smallest scope error handling function possible (in order: ISNA, ISERR, ISERROR, IFERROR)

I’m sure you guys can poke some serious holes in that list, so please do. I guess my point is that errors are good. When you see an error returned in Excel, you know you made a mistake and you can fix it. If you don’t see it, you may not know and you may not fix it. Some errors are predictable and unavoidable, so we use the tools we have to deal with those errors in a responsible manner.