D
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
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