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,),"")

 

[MARK858]

ISBLANK and LEN=0 are 2 different things, 1 recognises empty strings and the other doesn't and so isn't an accurate comparison.
Having stated that I was always told that Len was preferable but I have never done any tests to verify that.

 

[Jonmo1]

Yep, there are considerations other than 'speed' that determine which is the better option to use.

ISBLANK will fail to recognize a formula blank, i.e. IF(contdition,"",result)
LEN will work for that.

Personally, I never use ISBLANK for that very reason.

But if you're dealing with TRUE blanks, then either function is just fine.
Any difference in 'speed' would be infantesimal.

You can also use
=IF(A1="","",VLOOKUP())

 

[mole999]

10,000 rows but your using the entire column A:A which maybe far too many

 

[eggyjla]

@mole999 - I was just giving an example of the # of rows I was dealing with and that the formulas are in the rows mentioned. The formulas do not extend the entire column, only to row 11,247.

@ Jonmo1 - Yes I'm really dealing with empty or blank cells, not cell values from formulas (ie "", 0)

 

[shg]

Looks like ISBLANK is a little faster:

Range​

Formula​

Cells​

Iterations​

TotalCalcs​

Time​

[td]

Time/Calc​

[/td][td]

RelSpeed​

[/td]
[tr][td]C1:C1000[/td][td] =ISBLANK(A1)[/td][td]

1,000​

[/td][td]

16,384​

[/td][td]

16,384,000​

[/td][td]

3.633​

[/td][td]

0.000 000 222​

[/td][td]

1.4​

[/td][/tr]
[tr][td]E1:E1000[/td][td] =LEN(A1)[/td][td]

1,000​

[/td][td]

8,192​

[/td][td]

8,192,000​

[/td][td]

2.570​

[/td][td]

0.000 000 314​

[/td][td]

1.0​

[/td][/tr]

 

[mole999]

Looks like ISBLANK is a little faster:

Range​

Formula​

Cells​

Iterations​

TotalCalcs​

Time​

<tbody>
[TD]

Time/Calc​

[/TD]
[TD]

RelSpeed​

[/TD]

[TR]
[TD]C1:C999[/TD]
[TD] =ISBLANK(A1)[/TD]
[TD]

999​

[/TD]
[TD]

16,384​

[/TD]
[TD]

16,367,616​

[/TD]
[TD]

3.352
​

[/TD]
[TD]

0.000 000 205​

[/TD]
[TD]

1.4​

[/TD]
[/TR]
[TR]
[TD]E1:E999[/TD]
[TD] =LEN(A1)[/TD]
[TD]

999​

[/TD]
[TD]

8,192​

[/TD]
[TD]

8,183,808​

[/TD]
[TD]

2.391​

[/TD]
[TD]

0.000 000 292​

[/TD]
[TD]

1.0​

[/TD]
[/TR]
</tbody>

Now I am confused, isn't 2.4 quicker than 3.4 , yes it did do twice as much work, but was it necessary

 

[shg]

Here's a more complete answer. The layout includes a column of text, a column of random numbers (as values), a column of blanks, and six formulas:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][/tr][tr][td]

1​

[/td][td]

Text​

[/td][td]

Number​

[/td][td]

Blank​

[/td][td][/td][td]

IsBlank(Text)​

[/td][td][/td][td]

IsBlank(Number)​

[/td][td][/td][td]

IsBlank(blank)​

[/td][td][/td][td]

Len(Text)​

[/td][td][/td][td]

Len(Number)​

[/td][td][/td][td]

Len(Blank)​

[/td][/tr]
[tr][td]

2​

[/td][td]Bob[/td][td]

0.367514​

[/td][td][/td][td][/td][td]

FALSE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][td]

3​

[/td][td][/td][td]

17​

[/td][td][/td][td]

0​

[/td][/tr]
[tr][td]

3​

[/td][td]Bob[/td][td]

0.637776​

[/td][td][/td][td][/td][td]

FALSE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][td]

3​

[/td][td][/td][td]

17​

[/td][td][/td][td]

0​

[/td][/tr]
[tr][td]

4​

[/td][td]Bob[/td][td]

0.454466​

[/td][td][/td][td][/td][td]

FALSE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][td]

3​

[/td][td][/td][td]

17​

[/td][td][/td][td]

0​

[/td][/tr]
[tr][td]

5​

[/td][td]Bob[/td][td]

0.694409​

[/td][td][/td][td][/td][td]

FALSE​

[/td][td][/td][td]

FALSE​

[/td][td][/td][td]

TRUE​

[/td][td][/td][td]

3​

[/td][td][/td][td]

17​

[/td][td][/td][td]

0​

[/td][/tr]
[/table]

Here are the results:

Range​

Formula​

Cells​

Iterations​

TotalCalcs​

Time​

[td]

Time/Calc​

[/td][td]

RelSpeed​

[/td]
[tr][td]E2:E1001[/td][td] =ISBLANK(A2)[/td][td]

1,000​

[/td][td]

16,384​

[/td][td]

16,384,000​

[/td][td]

3.664​

[/td][td]

0.000 000 224​

[/td][td]

2.0​

[/td][/tr]
[tr][td]G2:G1001[/td][td] =ISBLANK(B2)[/td][td]

1,000​

[/td][td]

16,384​

[/td][td]

16,384,000​

[/td][td]

3.758​

[/td][td]

0.000 000 229​

[/td][td]

2.0​

[/td][/tr]
[tr][td]I2:I1001[/td][td] =ISBLANK(C2)[/td][td]

1,000​

[/td][td]

16,384​

[/td][td]

16,384,000​

[/td][td]

3.719​

[/td][td]

0.000 000 227​

[/td][td]

2.0​

[/td][/tr]
[tr][td]K2:K1001[/td][td] =LEN(A2)[/td][td]

1,000​

[/td][td]

16,384​

[/td][td]

16,384,000​

[/td][td]

3.738​

[/td][td]

0.000 000 228​

[/td][td]

2.0​

[/td][/tr]
[tr][td]M2:M1001[/td][td] =LEN(B2)[/td][td]

1,000​

[/td][td]

8,192​

[/td][td]

8,192,000​

[/td][td]

3.715​

[/td][td]

0.000 000 453​

[/td][td]

1.0​

[/td][/tr]
[tr][td]O2:O1001[/td][td] =LEN(C2)[/td][td]

1,000​

[/td][td]

8,192​

[/td][td]

8,192,000​

[/td][td]

2.148​

[/td][td]

0.000 000 262​

[/td][td]

1.7​

[/td][/tr]

The IsBlank formulas are fastest, and all the same speed. Len(Text) is just as fast. Len(Blank) is next, and Len(number) brings up the rear as the slowest.

@mole: 1.4 is faster than 1.0.

 

[Aladin Akyurek]

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,),"")

I you are looking for speed, try to sort $M$1:$N$10 in ascending order on column M and keep that way. This allows you to invoke:

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

which is very fast.

 

[shg]

Amen to Aladin's suggestion. That would dwarf the difference between IsBlank and Len.