View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.misc
Charles Williams Charles Williams is offline
external usenet poster
 
Posts: 968
Default Optimising calculation time

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

"Excel_Kiwi" wrote in message
...
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.

"Charles Williams" wrote:

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

wrote in message
...
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(Z20,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