Calculation speed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JChips - 27 Sep 2006 09:17 GMT

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?

Reply to this Message

Niek Otten - 27 Sep 2006 09:34 GMT

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

Signature

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?

Reply to this Message

JChips - 27 Sep 2006 11:40 GMT

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.

> Please supply your formula and describe the data (sorted?)
> Do you use any macros or User Defined Functions?
[quoted text clipped - 5 lines]
> | 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?

Reply to this Message

Roger Govier - 27 Sep 2006 12:15 GMT

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

Signature

Regards

Roger Govier

> It has counta() and countif() formulas across 4 sheets. These totals
> are then
[quoted text clipped - 28 lines]
>> had no
>> | problem previously. Is there a simple trick I am missing?

Reply to this Message