ISBLANK vs LEN (most efficient)

[eggyjla]

I have 10,000+ rows where one of the two forms is copied into A:A, C:C, E:E, G:G and I:I. I'm wondering which of the two is the preferred approach for keeping the recalculate time low. Which should take less time or less computing resources, or are would they be the same? I created two files, one withe each formula and the file size of both workbooks appear to be nearly the same.

=IF(ISBLANK(B1),"",VLOOKUP(B1,$M$1:$N$10,2,))

=IF(LEN(B1),VLOOKUP(B1,$M$1:$N$10,2,),"")

 

[eggyjla]

@shg - Looks as if for the same number of cells to be calculated, LEN(blank) has the fastest compute time at 2.1 s followed by ISBLANK(text) at 3.6 s. If I'm interpreting the table correctly, although ISBALNK is performing twice the number of calcs at a quicker rate, the overall compute time of LEN(blank) is faster? LEN(text) has the same number of iterations as the ISBLANK(), that's interesting! So if the cells contained a mixed set of values (blank, #, text), LEN would have the lowest (quickest) average compute time of 3.2 s compared to ISBLANK at 3.7 s where of course the majority of preferred values would favor either LEN(blank) or ISBLANK(text).

 

[shg]

The code calculates each block of formulas 1, 2, 4, 8, ... times until the calculation of each group of formulas takes 2 seconds or more, just to get a level of precision with Timer. The Time column is irrelevant; it will always be between 2 and 4 seconds. What's relevant is Time/Calc, which is Time / (Cells * Iterations). Smaller is better.

That's summarized in the Speed column, which is time/calc for the fastest formula divided by time/calc for this formula. In this column, just like a speedometer, bigger means faster. By that calculation, the SLOWEST formula always has a speed of 1.0.

 

[shg]

In response to a PM, the formula timer workbook is at https://app.box.com/s/b5ykwwa9x6xs8d7v9vhv