Excel efficiency question...

[Guest]

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?

 

[Tom Ogilvy]

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).

 

[Niek Otten]

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

 

[Guest]

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

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