Troubleshooting Excel 2003 calculation speeds
 

I have a couple of MS Excel 2003 files consisting of multiple worksheets that
are heavily formulated. I'm running into some issues with Excel just hanging
up when it's calculating.

For instance, when re-calculation is occurring, the "Calculating Cells: XX%"
is visible in the lower left corner of the window. Sometimes the XX values
will slowly reach 100, indicating completion of the calculation cycle. But
more and more, it's not completing and just hangs (for example, reaching
25%). I open up the Task Manager window and it will say the Excel is "Not
Responding".


Specific questions...
(1) How can I troubleshoot this?
(2) Is there way to summarize the number of formulas and compare with
thresholds above which performance problems may arise?
(3) Certain types of formulas that are definite troublemakers?

The most common formula I'm using has worksheets looking up values from
other worksheets (see example below). I will say there's a quite a number of
these formulas in my file doing a lot of cross referencing among worksheets.
But how can one know when you've gotten too large or "too many somethings" in
a given file.

=IF(COUNTIF('Table 1 & 2 combined DBF'!$G$2:$G$394,$H7) 0, OFFSET('Table 1
& 2 combined DBF'!$G$2,MATCH($H7,'Table 1 & 2 combined DBF'!$G$2:$G$394) - 1,
8), "")

This formula looks to see if a station number exists in a range within
another worksheet...if so, then find the row number, then move 8 columns over
to the corresponding value or site attribute.

I need some help here learning what or how I can troubleshoot these
situations. I would like to avoid splitting the Excel file into smaller
portions, because I'm not certain that's the sole problem.

This is becoming a real matter of frustration here...any guidance
appreciated...thanks.

JCWeaver

Troubleshooting Excel 2003 calculation speeds
 

For lots of information about Excel's calculation speed and how to improve that, visit Charles Williams' site:

www.decisionmodels.com

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JCWeaver in Raleigh" <JCWeaver in wrote in message
...
|I have a couple of MS Excel 2003 files consisting of multiple worksheets that
| are heavily formulated. I'm running into some issues with Excel just hanging
| up when it's calculating.
|
| For instance, when re-calculation is occurring, the "Calculating Cells: XX%"
| is visible in the lower left corner of the window. Sometimes the XX values
| will slowly reach 100, indicating completion of the calculation cycle. But
| more and more, it's not completing and just hangs (for example, reaching
| 25%). I open up the Task Manager window and it will say the Excel is "Not
| Responding".
|
|
| Specific questions...
| (1) How can I troubleshoot this?
| (2) Is there way to summarize the number of formulas and compare with
| thresholds above which performance problems may arise?
| (3) Certain types of formulas that are definite troublemakers?
|
| The most common formula I'm using has worksheets looking up values from
| other worksheets (see example below). I will say there's a quite a number of
| these formulas in my file doing a lot of cross referencing among worksheets.
| But how can one know when you've gotten too large or "too many somethings" in
| a given file.
|
| =IF(COUNTIF('Table 1 & 2 combined DBF'!$G$2:$G$394,$H7) 0, OFFSET('Table 1
| & 2 combined DBF'!$G$2,MATCH($H7,'Table 1 & 2 combined DBF'!$G$2:$G$394) - 1,
| 8), "")
|
| This formula looks to see if a station number exists in a range within
| another worksheet...if so, then find the row number, then move 8 columns over
| to the corresponding value or site attribute.
|
| I need some help here learning what or how I can troubleshoot these
| situations. I would like to avoid splitting the Excel file into smaller
| portions, because I'm not certain that's the sole problem.
|
| This is becoming a real matter of frustration here...any guidance
| appreciated...thanks.
|
| JCWeaver
|