Choice of function

[JackDanIce]

Hi,

Is there a list that tells me what functions execute faster than others or how can I work this out for myself?

A problem posted yesterday asked for a YES or NO output if a value existed in a range or not. Two solutions were suggested:

=IF(ISNUMBER(MATCH(B1,$A$1:$A$100,0)),"Yes","No")

and

=IF(COUNTIF($A$1:$A$100,B1)>0,"Yes","No")

And, similarly, I know how INDEX(MATCH for large data sets is (can be?) faster than VLOOKUP for the same result

So curious to know when using functions, if my sole objective was fastest calculations/execution, what to consider and how?

Thoughts please, thanks,
Jack

 

[Aladin Akyurek]

Hi,

Is there a list that tells me what functions execute faster than others or how can I work this out for myself?

A problem posted yesterday asked for a YES or NO output if a value existed in a range or not. Two solutions were suggested:

=IF(ISNUMBER(MATCH(B1,$A$1:$A$100,0)),"Yes","No")

and

=IF(COUNTIF($A$1:$A$100,B1)>0,"Yes","No")

And, similarly, I know how INDEX(MATCH for large data sets is (can be?) faster than VLOOKUP for the same result

So curious to know when using functions, if my sole objective was fastest calculations/execution, what to consider and how?

Thoughts please, thanks,
Jack

See post #8 in:

http://www.mrexcel.com/forum/showthread.php?t=38643

 

[JackDanIce]

Thank you Aladin, so the takeaway is where possible use MATCH if after fastest calculation.

However, given the relative closeness between VLOOKUP and MATCH, if MATCH is combined with INDEX, is the combined function slower than VLOOKUP alone?

 

[Richard Schollar]

Jack

Cast your eye over this thread:

http://www.excelguru.ca/forums/showthread.php?132-INDEX-MATCH-versus-VLOOKUP

 

[JackDanIce]

Hi Richard,

An interesting read, even recognised Jon's name!

My conclusion would be, for most people's requirements, when data is not sorted (and assuming unique lookup values) for an exact (linear) match, VLOOKUP is generally speaking faster than a combined INDEX MATCH, if the lookup column is the leftmost column.

Anything where the lookup column isn't the first column, INDEX, MATCH may be a better suggestion.

Thank you for directing me to that link,
Jack

 

[T. Valko]

Hi,

Is there a list that tells me what functions execute faster than others or how can I work this out for myself?

A problem posted yesterday asked for a YES or NO output if a value existed in a range or not. Two solutions were suggested:

=IF(ISNUMBER(MATCH(B1,$A$1:$A$100,0)),"Yes","No")

and

=IF(COUNTIF($A$1:$A$100,B1)>0,"Yes","No")

And, similarly, I know how INDEX(MATCH for large data sets is (can be?) faster than VLOOKUP for the same result

So curious to know when using functions, if my sole objective was fastest calculations/execution, what to consider and how?

Thoughts please, thanks,
Jack

You can test the calculation speed and see for youself.

There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

I think there's a free sample file here and you can always get the full featured commercial version called FastExcel:

http://www.decisionmodels.com/

The speed difference between 2 similar formulas may be on the order of 0.000025 seconds.

Are you able to notice the difference?

When you see buzz words describing a formula as "expensive", take those with a grain of salt.

If my file is 50kb in size do I need to be concerned with speed of formula calculation?

IMHO, efficiency is good to know but it's not the end all! I think you should start with the simplest formula construct and then go from there if you need to account for certain conditions and efficiency.

I can sum up my whole philosopy towards Excel in a single word:

KISS

Keep It Simple Stupid

 

[Aladin Akyurek]

Thank you Aladin, so the takeaway is where possible use MATCH if after fastest calculation.

However, given the relative closeness between VLOOKUP and MATCH, if MATCH is combined with INDEX, is the combined function slower than VLOOKUP alone?

Regarding [1] INDEX/MATCH vs [2] VLOOKUP with match-type set to 0 (where the underlying algorithm is probably a form of linear search), the former is believed to be a tad faster.* John appears to forward a different temporal profile, favoring the latter (see Richard Scollar's post quoting the relevant link).

Apparently, similar results are also available for the match-type set to 1 (where the underlying algorithm is probably a form of binary search).

Temporal profiling is a difficult 'art'. Code used can differ in how they take into account the various factors in the environment in which the tests are done.

________________________
Ragdyer confirmed this to me once at microsoft.public.excel.worksheet.functions

 

[JackDanIce]

The speed difference between 2 similar formulas may be on the order of 0.000025 seconds.

Are you able to notice the difference?

Is the sun slightly warm to touch? Of course I can!

I think you're right and it alludes to my previous reply. For most people's requirements in day-to-day Excel use KISS would be best.

Thanks Valko,
Jack

 

[T. Valko]

Is the sun slightly warm to touch? Of course I can!

I think you're right and it alludes to my previous reply. For most people's requirements in day-to-day Excel use KISS would be best.

Thanks Valko,
Jack

You're welcome!

I think I'm going to add the "KISS" reminder to my signature.

KISS needs all the exposure it can get!

 

[JackDanIce]

Valko - just don't ask for tongue! I'd join in with you, but I'm too busy finding quotes from comedies I like. It's just the way it rolls!