Extremely slow recalculate after adding cell reference

[Jeff Evans]

Excel 2002 (10.6789.6735) SP3
TreePlan add-in (not used in referenced spreadsheet)


Hi,

I have a spreadsheet with a large number of random numbers being
calculated to perform what is essentially Monte Carlo integration. I'm
having a very hard time understanding how the calculations work in
Excel, such that the calculation time required can increase by many
orders of magnitude, after adding a single cell reference. To see what
I mean, please download my spreadsheet:

https://netfiles.uiuc.edu/jwevans1/www/temp/spreadsheet.zip

I have manual calculation disabled. When I manually recalculate (F9),
it finishes in two seconds or less, with all the numbers in the large
table being updated as well as the values at the top of the sheet.
However, suppose I want to add another value at the top which references
anything in the table, or another value derived from the table. For
example, it could be something as simple as setting any empty cell equal
to D4. After adding this new value (which depends directly or
indirectly on the values in the large table), the time for the NEXT
recalculation takes on the order of minutes or perhaps longer (I didn't
wait around).

My original intention was to add some statistics such as standard
deviation, standard error, etc. but found that even contrived
"calculations" cause the massive slowdown as well.

Can anyone shed some light on what is going on, or how to fix it? Thanks.

 

[Mike Middleton]

Jeff -

I think Excel builds the calculation dependency tree the first time that a
worksheet is calculated. When you add another formula, e.g., a reference,
Excel must rebuild the calculation tree. If you do wait for it to complete
the rebuild, you should find that subsequent recalculations are much faster.

If you want to learn more, the standard source is Charles Williams' web site
at
http://www.decisionmodels.com/calcsecrets.htm
and related pages at that site.

- Mike
(author of the TreePlan decision tree add-in, completely unrelated to your
current problem)
www.mikemiddleton.com

 

[Jeff]

For this type of calculation take a look at DecisionPro ( or for such
calculations on the web, try DecisionScript) - both by Vanguard
Software are excellent for modeling with Monte Carlo simulations and
statistics built in.

* * Please include a copy of this message with your reply

Jeff Bennett
(e-mail address removed)

* Bennet-Tec Information Systems, Inc
* 50 Jericho Tpk, Jericho, NY 11753
* Phone 516 997 5596, Fax - 5597
* RELIABLE Components Make You Look Sharp!
* TList/Pro * ALLText HT/Pro * MetaDraw *
* Custom Software Development Services Too.
* WWW.Bennet-Tec.Com

=================== ===================