Calculation speed

J

JChips

I have a not hugely complex workbook which I have been running without fuss.
Now, on machines upgraded to use Excel 2003, every time you enter a new piece
of info it takes up to a minute to calculate whilst the same file is fine on
the previous version of Excel. I can turn the automatic calculation off and
there is no delay but it seems odd to have to do this given that it had no
problem previously. Is there a simple trick I am missing?
 
N

Niek Otten

Please supply your formula and describe the data (sorted?)
Do you use any macros or User Defined Functions?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a not hugely complex workbook which I have been running without fuss.
| Now, on machines upgraded to use Excel 2003, every time you enter a new piece
| of info it takes up to a minute to calculate whilst the same file is fine on
| the previous version of Excel. I can turn the automatic calculation off and
| there is no delay but it seems odd to have to do this given that it had no
| problem previously. Is there a simple trick I am missing?
 
J

JChips

It has counta() and countif() formulas across 4 sheets. These totals are then
compared, averaged etc.

It is a way of keeping track of a reward (stars) system in a school. The
staff enter their initials by a child and the computer counts how many each
child has and counts how many each member of staff has givena nd for which
subject etc etc.

The strange thing is that the calculation problem only seems to happen on
the machines that have been upgraded.

Thanks for your help.
 
R

Roger Govier

Hi

There was a change to the calculation engine that took place from XL2002
onward.
I believe that I read somewhere (but can't find it now), that the first
time a Workbook created in a version earlier than 2002 is opened, Excel
has to go through a process to optimise the calculation method. If these
file is then saved under the new Excel version, this process will not
need to be repeated, and there should be no further speed reduction.

If the file is being modified on an XL2000 machine, and then opened on
an XL2002/3 machine, the process will probably occur each time.

Take a look around Charles William's site for more information on how
Excel calculates
http://www.decisionmodels.com/calcsecrets.htm

and if you are feeling very strong(!!!), there is a huge article on the
Microsoft website that deal with the methodology of recalculation
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlrecalc.asp
 
Top