Optimising calculation time

[davespunk]

Hi,

I'm currently using a spreadsheet which calculates decay rates. The
spreadsheet consists of 60 calculation columns by n rows (n varies
depending on how many samples I use). The spreadsheet works as
follows:

In cell X1 the user puts the decay rate (i.e. 1%)
In cell D2 the user puts the starting value (i.e. 100)
In cell Z2 the user puts the initial value to be subtracted from the
starting value (i.e. 10)

In cell AB2, there is the fomula
=IF(SUM($D2-SUM($AA2:AA2)>0),IF(Z2>0,MIN((AA2*(1-$X$1)),SUM($D2-
(SUM($AA2:AA2)))),0),0)
which is dragged 60 columns to the right.

Output in cells AB2 and onwards would be 100, 99, 98, 97 etc until the
sum of all the values exceeds the starting value.

In Excel 2003 this spreadsheet would calculate in a matter of
seconds. However, since my department has upgraded to Excel 2007
(this is now a native 2007 spreadsheet, not a 2003 worksheet in
compatibility mode) this spreadsheets opening/calculating and,
particularly, saving time has increased exponentially. If the
spreadsheet contains more than 3000 or so rows, I now have to leave
the spreadsheet to save overnight (!).

Can someone help me to either optimise the spreadsheet or the formulas
within it so that I don't have to wreck my sleeping pattern everytime
I have to perform some urgent calculations?

Thanks,
Dave

 

[JLatham]

I need to ask at least one question before continuing: In your formula,
where it shows IF(Z2>0, should that be $Z2 or $Z$2 instead of just plain Z.
If it is just Z2, then that value is going to become AA2, AB2, AC2, etc.
Just need to know if that is the intent or not.

My first impulse is to tell you to throw $$ and hardware at it: either
faster machine to get your performance under 2007 back as it was with 2003,
or to come up with a machine you can retrofit with 2003 to use at time
critical moments.

 

[ShaneDevenshire]

Hi,

Here is an extensive discussion of this topic:

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

with lots of recommendations.

 

[JLatham]

One minor improvement would be to get rid of a couple of the unnecessary
SUM() statements, which would result in this formula:
=IF($D2-SUM($AA2:AA2)>0,IF(Z2>0,MIN((AA2*(1-$X$1)),$D2-(SUM($AA2:AA2))),0),0)

I'm not absolutely certain about this one, but I believe it will do the job
for you also. You would have your regular formula in AB2, and this one would
go into AC2 and be extended out for the remaining 59 columns:
=IF($D2-SUM($AA2:AB2)>0,IF($Z2>0,MIN((AB2*(1-$X$1)),$D2-AB2),0),0)
You can do this because the cell immediately to the left should [I think]
have the sum of $AA2:AAn-1 in it. At least it worked with the simple test
data you gave us.

OH - what is in AA2 to begin with?

 

[JLatham]

Yes, thanks for reminding me. I've had that link laying around for a while.
There are some potential pitfalls in it though - not the author's fault;
comes from the actual implementation of 2007 (the article was written as of
Beta 2).
Specifically at this point I'm thinking of VBA (or user defined function)
development. There are situations involving some commands (no, sorry, don't
have a list but I know that RND() is one) in VB on a system with multi-core
CPUs that are distinctly slower than on a single-core system. By distinctly,
I mean as in the RND() should have been called 140 times, but on a multi-core
system it gets called 11,000+ times. Oops.

 

[Charles Williams]

Hi,

Could you be more specific, or send me an example, on your 2007 RND()
problem etc. ?

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[davespunk]

I need to ask at least one question before continuing:  In your formula,
where it shows IF(Z2>0,  should that be $Z2 or $Z$2 instead of just plain Z.  
If it is just Z2, then that value is going to become AA2, AB2, AC2, etc.  
Just need to know if that is the intent or not.

My first impulse is to tell you to throw $$ and hardware at it: either
faster machine to get your performance under 2007 back as it was with 2003,
or to come up with a machine you can retrofit with 2003 to use at time
critical moments.

Thanks for your reply. Yes Z2 is used in the formula so adjacent
cells will be AA2, AB2 etc.

My current machine is 2.3Ghz AMD quad-core processor with 4Gb RAM so
it's not as if I'm running the spreadsheet on an old Celeron PC. The
frustrating thing is that Excel 2003 can handle this a lot better but
with the upgrade to Excel 2007 I was hoping to see some 'benefits'.

 

[Charles Williams]

Hi,

Which Excel 2007 format are you saving in? (.xls, .xlsb, .xlsx , ...)

If you can send me an example workbook I would be happy to take a look at
it.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[JLatham]

One thing that might help on the hardware side, if you are running Vista,
would be to get a ReadyBoost capable USB device (thumb drive) and attach it
and use it as cache RAM. See
http://www.microsoft.com/windows/windows-vista/features/readyboost.aspx for
more information.

I have 2 systems running Vista and Office 2007. One is a 2-core Intel CPU
at 2.4 Ghz with 2GB RAM and ReadyBoost; the other is a 4-core Intel CPU
running at 2.83 CPU with Vista 64-bit and 6GB RAM and ReadyBoost. The
performance difference between the two is very noticeable.

Your experience with Excel 2007 is not all that unusual from what I've seen
here in these forums. Nor is it different from some of my own personal
experiences with 2007 vs 2003. Be glad you're not graphing that data! First
time I ran into the real speed difference at times was when we moved an
application capturing lab equipment results and graphing them into 2007. The
whole process in 2003 took about a minute and a half. In 2007 on 2 different
machines (both dual-core CPUs and with more memory and faster clock than the
2003 system) it took between 10 and 11 minutes to complete - all the added
time was in graphing the data.

Try the new formula(s) I posted here, if that last one works properly, you
could get around a 20% performance boost using it. Just getting rid of the 2
extra SUM() statements appears to offer about a 5% performance boost.

 

[Excel_Kiwi]

I don't want to be specific on which formulas can be changed to increase the
speed, but speaking generally, Excel 2007 takes an hour to recalculate what
Excel 2003 does in about a minute on the same machine. I use an Intel Core 2
CPU with 4 GB ram. Now I wonder why I spent so much to upgrade MS Office.

 

[JLatham]

Charles,
Sorry for the delay - system didn't notify me about your post.

I'd be happy to. Drop me an email to [remove spaces]
Help From @ JLathamSite. com
and I can send an example. But it was really a simple thing - the user
needed to fill 150 cells with a set of random numbers that would remain
static until he requested another set. So a simple UDF was created that
looped thru the 150 addresses and generated a random entry for each. In
Excel 2003 the response was blink-quick, but in 2007 you could see the
filling of the cells so I investigated and couldn't see why. Sent the file
to MSFT and they analyzed it deeper and informed me of the problem with some
work-around code that disables multi-core use and later re-enables it. Of
course the trick is to know when to use that code and when to go back to
taking advantage of the multiple cores; and frankly I don't know of a list of
affected commands that would help make that decision.

 

[Charles Williams]

Most but not all of the workbooks I haver tested calculate faster in Excel
2007 than in Excel 2003.
(see http://www.decisionmodels.com/VersionCompare.htm for details of the
timings etc).

None of the workbooks I have tested show anything remotely like the time
difference in favour of Excel 2003 you mention.

Trying to duplicate the decay calculation in the post below it calculates
very fast for 3000 rows (0.2 secs for a full calculation) so obviously I
have not duplicated the problem.

I really would appreciate an example workbook or sufficiemt details to be
able to duplicate the problem.

regards
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com