The IFERROR(…) Function Again!

I know that not everyone has the time, or even the energy, to update every page on their web site or every post in their blog, myself included; so it’s no surprise that we can still find pages with examples that discuss =IF(ISERROR(…)).

The purpose of this page, then, is to convert a couple of examples to the more up to date and appropriate =IFERROR(…) function.

Look at this ARRAY formula =SUM(IF(ISERROR(C5:C13),0,C5:C13)) … what is it trying to do? It says add everything together in the range C5:C13 and if there is an error, ignore it and concentrate on the good values in that range.  You MUST enter this as an ARRAY formula otherwise it will not work.

Now consider this more efficient ARRAY formula alternative =SUM(IFERROR(C5:C13,0)) … try it, it really works! The IFERROR(…) function came with Excel 2007 and is much more direct and easy to understand than the older version. Again, you must ARRAY enter this formula or it won’t work.

Now consider this ARRAY entered formula =AVERAGE(IF(ISERROR(C5:C13),FALSE,IF(C5:C13=””,FALSE,C5:C13))) … what does it do? Well, try to understand it on your own. Better still, consider the more up to date version that does exactly the same by ARRAY entering this =IFERROR(AVERAGE(C5:C13),AVERAGEIFS(C5:C13,C5:C13,”>0″)) … still complicated isn’t it?

Now try the following NON ARRAY entered and NON IFERROR(…) alternative =AVERAGEIFS(C5:C13,C5:C13,”>0″) … this one is much more direct and much easier to use!

It is worth your while to learn the new Excel 2007 and 2010 functions: after all, there aren’t that many of them!