• Excel Book Excerpt

Excel Avoid Errors Using IFERROR

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Avoid Errors Using IFERROR

Problem: I’ve written a brilliant formula. Sometimes, due to the incoming data, the formula generates an error. How can I suppress the errors?

Strategy: Starting in Excel 2007, Excel offers the amazing IFERROR function. Don’t confuse this with the =ISERROR(), =ISERR(), =ISNA() functions. This new IFERROR function is very cool.

Before Excel 2007, to head off errors, you would have to test for any of the conditions that would cause an error. This might mean taking the very long formula from Figure 414 and making it insanely long:

=IF(COUNTIFS($D$2:$D$57,I$1,$D$2:$D$57,I$2,$E$2:$E$57,$H3)=0,"--",AVERAGEIFS($F$2:$F$57,$D$2:$D$57,I$1,$D$2:$D$57,I$2,$E$2:$E$57,$H3))

There are 20 cells in Figure 414, and 19 of them are working just fine. Yet, this formula will force Excel to do a complete SUMIFS before it can figure out if it should put -- or go on to calculate the AVERAGEIFS. This a huge amount of complexity just to zap one #DIV/0! error.

LE10000411.jpg

Figure 415 Before Excel 2007, error handling was slow and complex.

Handling errors with IFERROR is dramatically easier. Say that you have any formula. Edit the formula. Type IFERROR( after the existing equals sign. Go to the end of the formula, type a comma, then what you want to have happen in case there is an error, then the closing parenthesis.

Say that you had a formula of =Formula.

  • To replace errors with a zero, use =IFERROR(Formula,0)
  • To replace errors with --, use =IFERROR(Formula,"--")
  • You can specify another formula: =IFERROR(Formula,OtherFormula).

Think about most data sets where some errors occur. You probably have more than 95% of the cells that calculate without an error and less than 5% that generate errors. The IFERROR function is smart enough to try the first calculation and only move on to the second argument when it gets an error. This will be a drastic time savings when you don’t have to use =IF(ISNA(VLOOKUP()),"--",VLOOKUP()) anymore.

LE10000412.jpg

Figure 416 IFERROR is new in Excel 2007.

Gotcha: While the ratio is now below 10%, there are still some people using Excel 2003 or earlier. If you have to share your workbook with any of those people the IFERROR will return a #NAME? error.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:



Popular Pages

More Info