Error 2036 from Rnd?

[AlexR688]

Hi everyone,

I am trying to run a Monte Carlo simulation and keep getting a very strange error.

At a random point during the run (it differs every time), I will get the error 2036 message when Excel tries to generate a random number using Application.Norm_S_Inv(Rnd()). I have also tried using Application.NormInv(Rnd(), 0, 1) but that suffers the same fate.

Could it be that Excel cannot handle generating so many random numbers and just gives up? This works fine for smaller number of paths/points in time, just not for larger ones.

I am truly stumped.

Thanks in in advance for your help.

 

[Rick Rothstein]

According to the help files for Excel's NORMSINV function (which I assume is the same Application.Norm_S_Inv calls)...

"If probability <= 0 or if probability >= 1, NORMSINV returns the #NUM! error value"

Since the Rnd() function can generate 0 for one of its results, my guess is your error is generated when that happens. I think you are going to need to assign the output of Rnd() to a variable and then test to see if that variable is greater than 0 before you pass it into the Norm_S_Inv function call.

 

[AlexR688]

Rick,

That works perfectly. Thanks so much!

For anyone else with the same issue:

I added two global constants "RndLow" and "RndHigh" then entered an If statement that says to check if Rnd<=0 or Rnd>=1. i.e.

Global Const RndHigh = 0.999999999999999
Global Const RndLow = 0.000000000000001

RandomizeRndA0 = Rnd()
If RndA0 <= 0 Then
RndA0 = RndLow
ElseIf RndA0 >= 1 Then
RndA0 = RndHigh
End If
RndB0 = Rnd()
If RndB0 <= 0 Then
RndB0 = RndLow
ElseIf RndB0 >= 1 Then
RndB0 = RndHigh
End If

 

[Rick Rothstein]

Rick,

That works perfectly. Thanks so much!

For anyone else with the same issue:

I added two global constants "RndLow" and "RndHigh" then entered an If statement that says to check if Rnd<=0 or Rnd>=1. i.e.

Global Const RndHigh = 0.999999999999999
Global Const RndLow = 0.000000000000001

RandomizeRndA0 = Rnd()
If RndA0 <= 0 Then
RndA0 = RndLow
ElseIf RndA0 >= 1 Then
RndA0 = RndHigh
End If
RndB0 = Rnd()
If RndB0 <= 0 Then
RndB0 = RndLow
ElseIf RndB0 >= 1 Then
RndB0 = RndHigh
End If

Some comments about your code...

1) The maximum value for the Rnd function is a number less than 1, so you do not need the RndHigh constant nor do you have to test against it.

2) The Rnd function returns a Single, so your RndLow should be 0.0000001. I think the only reason your number is working is because NORM_S_INV must take a Double and so it sees 0.000000000000001 as not being 0.

3) I am not sure how the code you showed us fits in with the rest of your code, but you should know that the Randomize function (which got accidentally concatenated with the RndA0=Rnd() line of code in the code snippet you posted) should only be run once per session. See my mini-blog article here for a demonstration as to why...

<!-- title / author block -->[h=3]VB's Randomize Function Should Be Run Only Once Per Session[/h]