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>

 

[Damon Ostrander]

Hi PWONG11,

Was the original problem that necessitated the use of a non-volatile version of RAND due to the fact that RAND was called twice in the IF function of the formula, therefore yielding two different random numbers? If so, there is a way of having the best of both worlds, using a UDF to enable using the volatile RAND function, but using it in each formula only once.

I should also point out that BETAINV in called (and evaluated) twice in each formula. This also is inefficient as it appears that it is needed only once.

Damon

 

[mikerickson]

Damon,

Are you talking about using the worksheet function Rand() rather than VBA's Rnd ?

My testing shows that VBA Rand is 3% faster than RAND

Function NVRand1(Trigger As Variant) As Double
    NVRand1 = Evaluate("RAND()")
End Function

Function NVRand2(Trigger As Variant) As Double
    Randomize
    NVRand2 = Rnd()
End Function

 

[shg]

@mike,

I think the difference is a lot more than that -- like hundreds of times longer for RAND()

Sub Test()
    Const n As Long = 100000
    Dim i As Long
    Dim t As Single
    Dim f As Single
    Dim d As Double
    

    t = Timer
    For i = 1 To n
        f = Rnd()
    Next i
    MsgBox Timer - t
    

    t = Timer
    For i = 1 To n
        d = Evaluate("=rand()")
    Next i
    MsgBox Timer - t
End Sub

@pwong,

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

 

[PWONG11]

Damon & SHG,

Thank you both for your responses.

Damon - in answer to your question, the reason for the non-volatile function was that I need to have the data & results remain static instead of changing on any action or entry in the workbook, even in unrelated cells. The data should only change when an entry is made in a cell referenced in the formula. It was not due to the "error-correcting" setup of the formula.

That said, your response/question made me realise that there was absolutely no need to use such a lengthy construct in my formula - scrolling a little further down, SHG has indeed spelt out what I should have been using in the first place (to which I'm feeling a little sheepish for not thinking of it in the first place since I had already used IFERROR in other parts of my workbook - I have no excuse).

SHG - thanks for this. I have modified the formula for every cell to use the IFERROR construct and although it is definitely faster, it is still quite slow using the standard volatile RAND() function.

Given that the volatile version is still so slow, I'm pretty sure that the performance improvement is not enough to make the non-volatile version of RAND() I have workable.

Thanks again for your responses so far, and I would welcome any other suggestions you may have on this.

PWONG11

 

[mikerickson]

@SHG, my testing showed NRand2 (the vba Rand function) was 3% faster than the Evaluate in NRand1.

 

[gregtx81]

How frequently are you changing values in rows 2:8?
What about a WorksheetChange event with Target of A2:T8, which will then place formulas on the top row, copy them down, then copy-pastevalue everything?

 

[shg]

I'm missing how a non-volatile version of RAND will speed anything up. You still have to calculate all those formulas once per iteration, right?

If there are other elements of the design that cause them to calculate multiple times, then perhaps set calculation to manual and have the calculation of specific ranges controlled with code. There not enough information to make a more specific suggestion.

BETAINV is surely pig-slow; it has to iterate the BETADIST function. If B7 and B8 are constant, you could make a table of BETADIST values, and then use a simple lookup formula to get the inverse. The table could have 1000, 10,000, or 100,000 rows; it would only have to be generated once, and the lookups would be lightning-fast.

That inverse would be coarse compared to using BETAIND(RAND(), ...), but perhaps adequate.

 

[PWONG11]

SHG,

Sorry - I might not have explained myself that well -

The use of a non-volatile version is not to speed things up - I actually need the function to be non-volatile. The problem is that it is so much slower than the volatile version (which in my construct is still slow but workable).

I am trying to look for ways to make a non-volatile formula construct faster - that being said, you have actually pointed out another thing for me to look into which is the other parts of the formula.

I hadn't thought about how fast/slow the BETAINV function was inherently and you make a good point about the B7 & B8 cells. They are variable between scenarios, but constant between simulation events. There is no reason why I can't calculate the inverse and other multipliers in a separate cell for each scenario and then just reference this in every simulation event for that scenario.

I will have a tinker with this and see how I go.

Once again, thanks very much for the steer on the next thing to try.

Cheers,

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

 

[shg]

I actually need the function to be non-volatile.

What does that mean? Surely not that all of the BetaInv formulas should use the same value of Rand().

I'm losing the bubble.