with SP2, has the rand() function been slowed down ?

[Guest]

I have a spreadsheet in which I do some Monte Carlo simulations and use the
rand() function in around 350000 cells. Before upgrading to excel 2003/SP2,
this sheet would calculate in around 20 seconds. After the "upgrade" it now
runs in around 90 seconds. Is there any way for me to recoup the speed
without going back to the old configuration ? Why the slowdown ?

 

[Guest]

Actually, the formula I am using is : normsinv(rand()), so the slowdown could
be caused by normsinv as well.

 

[Mike Middleton]

zan -

The slowdown is most likely due to the increased precision of the normal
inverse function in Excel 2003.

There are Knowledge Base articles at www.microsoft.com describing some of
the changes. The improvements in precision require more calculations.

Instead of using Excel's normal inverse function, you could substitute some
VBA code using the well-documented Box-Muller method for obtaining normal
random numbers. Search groups.google.com for examples.

Or, download the tryout version of my RiskSim add-in, available from
www.treeplan.com, and use its RandNormal function.

- Mike
www.mikemiddleton.com

 

[Guest]

Thanks Mike - I implemented Box-Muller and now it calcs in about 5 seconds,
which is entirely acceptable. I will also check out your tools.