Excel efficiency question...
 

Is there a good source that someone can point me to that will explain or rank
the various ways that excel can work with data according to how efficient the
code will work?

I.E. how does =Index(cellarray,match("lookupval",cellarray,0),H1 ) compare to
Copying the data for cellarray into a sorted cellrange then doing a vlookup.

Is there an add-in, tool, trick etc. that will observe workbook
recalculations and report on which sets of formula are the resource hogs?

--
Regards,
John

Excel efficiency question...
 

Vlookup doesn't require sorted data for an exact match.

If you want opinions on the efficiency of worksheet function approaches, you
would probably get a better audience in the worksheet.functions newsgroup
and post during the week rather than on a weekend.

that said, Charles Williams has written quite a bit on this topic

http://www.decisionmodels.com

He also has an addin that can be used for timing worksheet function
approaches (not free).



--
Regards,
Tom Ogilvy


"John Keith" wrote in message
...
Is there a good source that someone can point me to that will explain or
rank
the various ways that excel can work with data according to how efficient
the
code will work?

I.E. how does =Index(cellarray,match("lookupval",cellarray,0),H1 ) compare
to
Copying the data for cellarray into a sorted cellrange then doing a
vlookup.

Is there an add-in, tool, trick etc. that will observe workbook
recalculations and report on which sets of formula are the resource hogs?

--
Regards,
John

Excel efficiency question...
 

This site contains a lot of information about speed and memory usage. There
is a download for a measurement tool. I think it has to be paid for.

www.decisionmodels.com

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"John Keith" wrote in message
...
Is there a good source that someone can point me to that will explain or
rank
the various ways that excel can work with data according to how efficient
the
code will work?

I.E. how does =Index(cellarray,match("lookupval",cellarray,0),H1 ) compare
to
Copying the data for cellarray into a sorted cellrange then doing a
vlookup.

Is there an add-in, tool, trick etc. that will observe workbook
recalculations and report on which sets of formula are the resource hogs?

--
Regards,
John

Excel efficiency question...
 

That page looks exactly like what I was looking for,
thanks Niek and Tom


"Niek Otten" wrote:

This site contains a lot of information about speed and memory usage. There
is a download for a measurement tool. I think it has to be paid for.

www.decisionmodels.com

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"John Keith" wrote in message
...
Is there a good source that someone can point me to that will explain or
rank
the various ways that excel can work with data according to how efficient
the
code will work?

I.E. how does =Index(cellarray,match("lookupval",cellarray,0),H1 ) compare
to
Copying the data for cellarray into a sorted cellrange then doing a
vlookup.

Is there an add-in, tool, trick etc. that will observe workbook
recalculations and report on which sets of formula are the resource hogs?

--
Regards,
John