IFERROR Efficiency...

[jmwbowen]

This is a technical question about IFERROR.

I have a few formulas that depend on particular cells to contain data to function correctly. A lack of any data in those cells will result in an error value.

Previously, I trapped these errors using the following method:

=IF(ISBLANK(D3),"",formula)

I've now changed them to the following:

=IFERROR(formula,"")

These formulas are repeated MANY times down a column.

I do not know the exact order of operations within the IFERROR function, so I can't be sure which is faster. I am guessing the following:

The first example using the IF function must first evaluate the ISBLANK condition. If result is FALSE, then it evaluates the formula.​

Does IFERROR do one of the following?:

a. Evaluate formula for error. If no error, re-evaluate formula and give result.

b. Evaluate formula, store result in memory. If error, go to error portion, if NOT error, retrieve result from memory.​

With respect only to speed and efficiency, which way is better?

Thanks in advance,

Jason

 

[RoryA]

IFERROR does option b. I don't think you would see much, if any, efficiency improvement over your original formula by using IFERROR. It really comes into its own for formulas that are repeated, such as a typical VLOOKUP error handler:
=IF(ISERROR(VLOOKUP(....)),"Error",VLOOKUP(...))
where the VLOOKUP part was actually executed twice for any non-error results. Your formula has a fairly inexpensive data check to avoid the error.

 

[jmwbowen]

Thanks for the reply, Rory. I've been on an efficiency quest with this workbook since the end user is using an older computer and every little bit helps.

 

[tom_blamire]

IFERROR should really only be used on small data sets. Anything that is using a large data set (2000 rows+) i would use a logical test beforehand - speeds up calculation