Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normal Distrubtions with Upper/Lower Bounds
I want to generate random numbers in VBA. I also want to dictate the
upper and lower bounds of the random number. I know how to do this using the Rnd Function. As follows: RandomNum = Int((UpperBounds - LowerBounds) + 1)* Rnd + LowerBounds However, given the short comings of the RND function I have come across Myrna Larsons code for a normal distribtion (See below). How do you incorporate the upper/lower bound logic from above into Myrna's code? Thanks in advance. Function RandomNormal(Optional Mean As Double = 0, _ Optional SD As Double = 1) As Double 'This function is to enable to use normal distrubtion vs the Rnd function which is uniform 'This function can be called if a uniform distrubution is not warranted Static HaveX1 As Boolean Dim V1 As Double, V2 As Double, S As Double Static X1 As Double Dim X2 As Double If HaveX1 = False Then Do V1 = Rnd() * 2 - 1 V2 = Rnd() * 2 - 1 S = V1 * V1 + V2 * V2 Loop While S = 1# Or S = 0# S = Sqr(-2 * Log(S) / S) X1 = V1 * S X2 = V2 * S HaveX1 = True RandomNormal = X2 * SD + Mean Else HaveX1 = False RandomNormal = X1 * SD + Mean End If End Function 'RandomNormal Terry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normal Distrubtions with Upper/Lower Bounds
However, given the short comings of the RND function
Myrna's function is using the RND function. Also, if you want uniformly distributed random numbers, then you wouldn't use the normal distribution to get them. -- Regards, Tom Ogilvy wrote in message om... I want to generate random numbers in VBA. I also want to dictate the upper and lower bounds of the random number. I know how to do this using the Rnd Function. As follows: RandomNum = Int((UpperBounds - LowerBounds) + 1)* Rnd + LowerBounds However, given the short comings of the RND function I have come across Myrna Larsons code for a normal distribtion (See below). How do you incorporate the upper/lower bound logic from above into Myrna's code? Thanks in advance. Function RandomNormal(Optional Mean As Double = 0, _ Optional SD As Double = 1) As Double 'This function is to enable to use normal distrubtion vs the Rnd function which is uniform 'This function can be called if a uniform distrubution is not warranted Static HaveX1 As Boolean Dim V1 As Double, V2 As Double, S As Double Static X1 As Double Dim X2 As Double If HaveX1 = False Then Do V1 = Rnd() * 2 - 1 V2 = Rnd() * 2 - 1 S = V1 * V1 + V2 * V2 Loop While S = 1# Or S = 0# S = Sqr(-2 * Log(S) / S) X1 = V1 * S X2 = V2 * S HaveX1 = True RandomNormal = X2 * SD + Mean Else HaveX1 = False RandomNormal = X1 * SD + Mean End If End Function 'RandomNormal Terry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert lower to upper case automatically without using UPPER | Excel Discussion (Misc queries) | |||
Changing upper case characters to upper/lower | Excel Discussion (Misc queries) | |||
Change from mixed caps and upper lower to all upper lower case | Excel Worksheet Functions | |||
How to fix lower and upper bounds for data | Excel Worksheet Functions | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) |