G
Guest
I'm currently struggling with an MOAS (that's "Mother of All Spreadsheets")
that someone else at work has given to me.
It's 28 MB and takes several seconds to re-calculate. I'm in the process of
trying to diagnose ways in which I can make it more efficient (Ultimately
I'll need to run it as part of a Monte Carlo simulation--25,000 recalcs, so
every second is important).
I've run into a couple of peculiar Excel behaviors. Can anyone explain these?
1) I can do an F9 recalc on any sheet. However, there are a couple of sheets
(out of maybe 15) which, if I try to do a "Sheet Recalc" via Tools...Options
Calculation Tab, causes Excel to calculate indefinitely. (Or so it
seems...the percentage number on the status bar doesn't change, but the Excel
process shows 99% CPU use in Task Manager.) I discovered this when I was
trying to do individual sheet recalcs in a VBA macro to diagnose calculation
bottlenecks.
Anybody know why this happens or what I can do to fix it?
2) When I am connected directly to my office network, the recalc time for my
workbook is around 3 seconds. If I run it at home using the same machine,
while disconnected from the office network, the recalc time is 11 seconds.
The workbook has no external references whatsoever.
Does anyone know why this disparity occurs?
3) And finally, can anyone point me to resources available to diagnose and
speed up Excel calculations? I've seen the Knowledgebase and MSDN articles.
Are there diagnostic software tools or other sources of information that
would help me make this thing more efficient?
Sorry for the long post. Thanks for the help.
that someone else at work has given to me.
It's 28 MB and takes several seconds to re-calculate. I'm in the process of
trying to diagnose ways in which I can make it more efficient (Ultimately
I'll need to run it as part of a Monte Carlo simulation--25,000 recalcs, so
every second is important).
I've run into a couple of peculiar Excel behaviors. Can anyone explain these?
1) I can do an F9 recalc on any sheet. However, there are a couple of sheets
(out of maybe 15) which, if I try to do a "Sheet Recalc" via Tools...Options
Calculation Tab, causes Excel to calculate indefinitely. (Or so it
seems...the percentage number on the status bar doesn't change, but the Excel
process shows 99% CPU use in Task Manager.) I discovered this when I was
trying to do individual sheet recalcs in a VBA macro to diagnose calculation
bottlenecks.
Anybody know why this happens or what I can do to fix it?
2) When I am connected directly to my office network, the recalc time for my
workbook is around 3 seconds. If I run it at home using the same machine,
while disconnected from the office network, the recalc time is 11 seconds.
The workbook has no external references whatsoever.
Does anyone know why this disparity occurs?
3) And finally, can anyone point me to resources available to diagnose and
speed up Excel calculations? I've seen the Knowledgebase and MSDN articles.
Are there diagnostic software tools or other sources of information that
would help me make this thing more efficient?
Sorry for the long post. Thanks for the help.