Performance on calculation - 2003 vs 2007

[Guest]

Refer to http://www.decisionmodels.com/memlimitsc.htm

"Excel 2003 is officially limited to 1 Gigabyte (GB) of memory. This limit
appears to a limit on the working set memory used by bthe Excel process,
which is the memory reported by Windows Task Manager.

Excel 2007 is limited to 2 Gigabytes of memory for the Excel process under
Windows XP (Windows memory limit). This 2 Gigabyte limit is a limit on the
Virtual Memory address space. Virtual memory used by a process is larger than
the working set memory reported by Windows Task Manager, so the amount of
useable memory under Excel 2007 is considerably less than twice that of Excel
2003.
Because Excel 12 also requires more memory to store the indexes to the
increased number of rows and columns you may not be able to load larger
workbooks under Excel 2007 than was possible under Excel 2003."

Does it imply that Excel 2003 is little better than Excel 2007 on memory to
store the indexes to the increased number of rows and columns?
I work with a larger workbooks about 10 MB ~ 25 MB for office 2000 under
Window XP, If I upgrade to Excel 2003 / 2007, does anyone have any
suggestions on what the different in term of performance on calculation
speeds is about? Which one is performed faster on calculation? ignoring the
file size on memory to store the indexes, because I can change to larger HDD
over 250 GB.

Do you have any suggestions?
Thanks you for any suggestions
Eric

 

[Charles Williams]

Memory limits do not generally affect calculation speed, they just stop
Excel working when they are hit.

Calculation speed differences between Excel versions do exist, but they are
very workbook dependent.

Because the Excel 2007 (Excel 12) calculation engine can do multi-threaded
calculation it will generally do the second calculation on large workbooks
faster if you have a multi-core PC.

But by far the largest improvement in Excel calculation speed on
slow-calculating workbooks can be achieved by prioritising the parts of the
workbook that are using up the calculation time and optimising the worst
calculation formulae bottlenecks. It is often possible to achieve an
order-of-magnitude improvement in calculation speed.

see the calculation secrets and optimising speed pages on my website.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[Guest]

Thank you for your suggestions

"prioritising the parts of the workbook that are using up the calculation
time"
Do you mean to set this workbook to run at real time in priority under XP?

"and optimising the worst calculation formulae bottlenecks."
Do you mean to separate each workbook within the limitation of working
memory 1GB under Office 2003? it will be run faster with a list of small
files one by one, rather than one large file.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

 

[Charles Williams]

"prioritising the parts of the workbook that are using up the calculation

time"

Means finding out which formulae in the workbook are using (re-)calculation
time, putting the times in a list and sorting it so you have a priority list
of slow formulae.

"and optimising the worst calculation formulae bottlenecks."

means changing the slowest formulae to faster ones. (there is no point in
fiddling with formulae that are already fast)

There are lots of suggestions about how to do all this on my website.


regards
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

 

[Guest]

Thank you very much for suggestions

Under the workbook, there are so many formulae, do you have any trick to
measure which one takes the most (re-)calculation time?

Thank you very much for any suggestions
Eric

 

[Charles Williams]

Under the workbook, there are so many formulae, do you have any trick to

measure which one takes the most (re-)calculation time?

You can use the Rangecalc addin from my downloads page to time calculation
of blocks of formulae.

Or look at the VBA code in the measuring calculation time section at
http://msdn2.microsoft.com/en-us/library/aa730921.aspx

(or you can use FastExcel to automate it all, but it is not free)

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com