Options to speed up calculation of non-volatile RAND() function for Monte Carlo simulation?...

[PWONG11]

Hello,

I am currently developing a basic monte carlo simulation for planning, based on a beta distribution.

I have 12 worksheets, each with up to 20 columns - one for each different scenario (different max, mode, min variables), with each scenario being modelled for 2000 events (i.e. 2000 rows).

I had originally set it up using the RAND() function and it worked reasonably well (albeit very slowly in calculating - minutes). Each of the cells has the following formula (using a result cell from Column B as an example):

=IF(ISERROR(((BETAINV(RAND(),1/B$7,1/B$8))*(B$4-B$5)+B$5)*B$2),"",(((BETAINV(RAND(),1/B$7,1/B$8))*(B$4-B$5)+B$5)*B$2))

Rows 2-8 simply contain my various parameters for each scenario, hence the fixed reference for the row, but not the column to enable me to copy the formula over to cells in other columns.

I then had the need to set up the model with a non-volatile version of RAND() so that the results would not keep changing.

Following some information on the web, I have tried using a non-volatile version of RAND() based on the VBA Rnd function as a user-defined function.

Although this works, the problem is that my workbook is now unworkably slow (to the point of freezing my system) as the calculation using my non-volatile RAND() is much slower than the standard volatile RAND().

I only have a limited proficiency in VBA (apart from this UDF function which was based off code from the web, most of my macros are generated by recording rather than coding), but am almost certain that I need to find a VBA solution to my problem - I just don't know what this is or what it would look like.

Does anyone have any ideas on how I can speed up the calculations for my workbook?

Many thanks.<textarea id="adlesse_unifier_magic_element_id" style="display:none;"></textarea>

 

[PWONG11]

What I would like it to do is for the function to generate a random number on initial entry into the cell, but then only update if there is a change to any of the inputs to the cell.

Right now, I could be on another worksheet making an entry in a completely unrelated cell (e.g. writing a column header or typing in a date field) and that will cause all the random numbers to refresh/change as it does so every time the workbook computes/calculates.

From reading up on this, my understanding is that any function that updates whenever the workbook computes/calculates is a volatile function (like "NOW()", "TODAY()" etc). Functions that only recompute on entry/change of input data are described as non-volatile functions.

I can't simply turn off automatic calculation as that will affect every open workbook (since that setting works at application level) as well as every cell - even those that I want/need to update when I put in a new value.

I can't copy and "Paste Special" -> "Values" because it is too cumbersome to do for every worksheet, every time someone wants to model a change (I have 12 worksheets of monte carlo simulations feeding into another calculation worksheet that uses the outcomes of the MC simulations).

I hope this makes a bit more sense.

Thanks,

PWONG11

<textarea id="adlesse_unifier_magic_element_id" style="display:none;"></textarea>

 

[shg]

OK, you're welcome, good luck.