Random Number Generator

[fperri]

Hi,

I need to populate a table by generating random numbers from an array of seven values. I need to use their mean, and the standard deviation of this array, since I need the data to resemble the array as closly as possible.

I've found tons of random number generators online, but none that use the mean and standard deviation. Its been about three years since my statistics class in college and I'm drawing a blank.

Does anyone know how to do this in VB?

[fperri]

.......or even how to see the VB code behind the random number generator in Excel?

[Killer42]

.......or even how to see the VB code behind the random number generator in Excel?

I don't know whether it will help, but we have a couple of snippets of VB code to generate random numbers in our VB Articles section.

Also, you might try looking up Standard Deviation on Wikipedia for info on how to calculate it. They often include samples in various programming languages. (I'd try it and let you know whether it's any good, but I'm at work now, and my employer's system blocks it.)

[fperri]

I don;t know whether it will help, but we have a couple of snippets of VB code to generate random numbers in our VB Articles section.

Also, you might try looking up Standard Deviation on Wikipedia for info on how to calculate it. They often include samples in various programming languages. (I'd try it and let you know whether it's any good, but I'm at work now, and my employer's system blocks it.)

I looked at the snid bits of code and none of them used the mean and standard dev either. Wikipedia has tons of formulas, but I'm lost at which one to use. I found an article that gave the formula that Excel supposedly uses to generate random numbers but I had to change it a bit to get it to work.....now I'm stuck.....it generates the first three random numbers fine then it reaches a point where the remainder (or Mod) is the same and it repeats itself with the same three random numbers so I don't know if I just screwed up the formula..........is there any statistics wizzes out there?

Here is the original formula that I found:

The first random number:
random_number=fractional part of (9821 * r + 0.211327), where r = .5

Successive random numbers:
random_number=fractional part of (9821 * r + 0.211327), where r = the previous random number

This formula will provide up to 1 million different numbers

I plugged the mean where the 9821 was and the standard dev into where the 0.211327 is......I'm not even completely sure this is correct but if this is the formula that Excel uses (in data analysis tools) then they must be plugged into the formula somewhere since if you want a normal distribution it prompts you for both of them........the only other thing I can think of is to try and create a macro in Excel to call the random number generator and generate a sheet for each of my samples.......I just don't want to do it this way......I want to be able to enter the sample into my database, and have a module that will populate a table from the sample data.....this would be much faster.....but I've already spent two days on this and I'm just getting more confused.......:(

Here is my code.....

mean = 7540209.33
standardDev = 4877299.44
 
Set recSet = CurrentDb.OpenRecordset("TestRandomNumbers")
 
For i = 1 To 10000
        'FIRST RANDOM NUMBER
        If i = 1 Then 
 
            randNum = (mean * (0.5 + standardDev))
            numString = Format(randNum, ".######")
            'GET THE FRACTORAL PART OF THE NUMBER
            numOne = Mid(numString, InStr(numString, ".") + 1)
            'GET THE NUMERICAL VALUE
            numTwo = Val(numOne)
            'SET R TO THE FIRST NUMBER FOR SUCESSIVE RANDOM PART
            r = numTwo
        'SUCESSIVE RANDOM NUMBERS
        Else
            randNum = (mean * (r + standardDev))
            numString = Format(randNum, ".######")
            numOne = Mid(numString, InStr(numString, ".") + 1)
            numTwo = Val(numOne)
            r = numTwo
        End If
 
        With recSet
            recSet.AddNew
            recSet.Fields("RandomNumber").Value = numTwo
            recSet.Update
        End With
 
Next i
 
End Function

[fperri]

And of course.......right after I post all that....I find something that I think will work. Cept....its in VBA4.....but I don't think that's going to be a big deal.

http://www.anthony-vba.kefra.com/vba/vba4.htm

[kadghar]

Hi, im not pretty sure that i understood your problem, but seems than it can be solved by using an inverse normal.

You should generate a random number between 0 and 1 and then check out what value of a Normal (yourMean, yourSDev) gives you that number.

You can make a numerical integration where you start summing areas under your Normal(yourMean, yourSDev) until the area under the curve is as big as the random number you generated between 0 and 1.

I've made up this little code:

(and for generating a list of values, just run it many times in a FOR)

Public Function Normal(mn As Double, sd As Double, val As Double)
'This function will help us in the numerical integration
 
Normal = (1 / (2.506628274631 * sd) * Exp((val - mn) ^ 2 / (-2 * sd ^ 2)))
 
End Function
Public Function RndNumber(mean As Double, stdDev As Double)
 
'First thing we have to do is to generate a random number between 0 and 1
Dim x As Double
x = Rnd()
 
'Then we can find out using some numeric methods where our Normal integrates that value
Dim boo1 As Boolean
Dim y As Double
Dim step As Double
Dim Integration As Double
 
boo1 = False
'We are going to start integration from -4 std dev, and divide the range into 250 secctions
y = mean + (stdDev * (-4))
step = (stdDev * 8 / 250)
Integration = 0
 
While boo1 = False
    Integration = Integration + (Normal(mean, stdDev, y) + Normal(mean, stdDev, y + step)) / 2
    If Integration >= x Then
        boo1 = True
    Else
        y = y + step
    End If
Wend
 
RndNumber = (2 * y + step) / 2
End Function

Good Luck

[Killer42]

Thanks very much for the info, kadghar.

I'd just like to point out it may be asking for trouble to use the name val for your variable or parameter, given that Val() is the name of a built-in function (possibly not in all versions).

[fperri]

Thanks kadghar,

I wish I would have checked back right away because I ended up integrating what I found on the site I linked, but your code was much shorter. I just got caught up in what I was doing. Thank you for taking the time.

[kadghar]

Yei!!!

Thats always a nice kind of message to read.

And you can ask me any further statistics questions , i enjoy them very much.