Speed - it's more than just a movie
Posted by Jill-ann Cooper on April 05, 2000 6:46 PM
I need some advice. I can go two or three different directions with a tool I'm creating and absolutely MUST pick the quickest to run.
1) I have to perform several different calculations based on the age of a store (like projected sales, etc). Is it faster to calculate the age by month in a separate matrix and then do a lookup to that matrix every time I need to use it OR is it faster to calculate the age in the same cell in which I am running the sales calculation?
2) Which is faster an array formula or a lookup?
These are very important to me because I am about to run projections for 2500 stores over 130 periods (for 15 different variables). Size is not an issue (at least this time it isn't) but speed is. Please help!
Posted by Celia on April 06, 2000 2:31 AM
Jill-ann
It sounds like the array formula might be quicker but I don't really know(it may depend upon what you are actually doing).If you intend using a macro to do what you want, you can ascertain the time involved for each method by using test data and putting the following code in the macro :-
Dim t As Date
t = Now()
'PUT YOUR CODE HERE
MsgBox Format(Now() - t, "hh:mm:ss")
There is probably a better way of finding out what you need - there usually is.
Celia
Celia
Posted by Jill-ann on April 06, 2000 11:43 AM
Posted by Jill-ann on April 06, 2000 11:44 AM
I won't be running any macros to perform these particular functions. The real question is:
a)what's faster an array or a lookup?
Posted by Jill-ann on April 13, 2000 1:29 PM
Re: Speed - the $35 answer
I just bucked up the $35 to talk to MSFT. The answer is: Not much of a difference speed-wise between a Lookup and an Array formula.
There. Spread the news.
