Non Uniform Random Number Generator

[busboy10]

Hi All,

I am trying to figure out a way for Excel to generate a random number
that is based on a KNOWN distribution.

For example, I have downloaded monthly returns for the S&P and
histogramed it out to find the distribution with mean and stddev.

Now I would like Excel to generate a random monthly return based on
the historical distribution. The only way I have down this is to have
a randomly selected row get generated and use that return as my number

Is there a more elegant way?

Thanks in advance!

 

[Master Blaster]

use a normal ditribution macro with input values in cells C3 .. C6

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

regards

 

[joeu2004]

I have downloaded monthly returns for the S&P and
histogramed it out to find the distribution with mean and stddev.
Now I would like Excel to generate a random monthly return based on
the historical distribution.

Suppose the mean is in A1 and std dev is in A2. Then use:

=NORMINV(RAND(),A1,A2)

Caveat: RAND() is a volatile function. So the formula will be
recalculated after editing any cell in any worksheet of the workbook.
One way to avoid that is to use the myRand() UDF below. You can use
it exactly as you use RAND(). Or you can use myRand(X1), which will
recalculate any formula with that expression after you edit X1,
including simply selecting X1and pressing F2 then Enter.

Function myRand(Optional r As Range) As Double
myRand = Evaluate("rand()")
End Function

To enter myRand(), press atl+F11 to open a VBA window, click Insert >
Module to open a VBA module editing pane, and copy-and-paste the above
text into the editing pane. You can now close the VBA window.