Help picking a random number from a given distribution

[Guest]

Is there a way for me to have Excel select a random number from a given,
e.g., normal distribution? Also can I put a range on the distribution?

Thanks in advance

 

[Guest]

If you use the function =RAND() it will generate a random number between 1
and 0. If you want it to be between 1 and 100 use =RAND()*100.

 

[Guest]

Theoretically, =NORMSINV(RAND()) would be a normal random number. In
practice, prior to Excel 2003 NORMSINV() was too inaccurate for for this to
be acceptable even for non-stringent applications. Alternately you could use
Ian Smith's inv_normal() function instead of the native Excel function
NORMSINV()
http://members.aol.com/iandjmsmith/Examples.xls

Another approach would be to use the Box-Muller method
=SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())
or
=SQRT(-2*LN(RAND()))*SIN(2*PI()*RAND())

Jerry

 

[Guest]

That will draw a random number from a uniform distribution. I'm looking for
one from a normal (bell shaped) or other distribution. I assume I'll need to
input a mean and standard deviation as well as specify the distribution, but
I'm having some difficulty determining if Excel can do it.

McGinty

 

[Guest]

Thanks, that's helpful. I'm using Excel 2003 so I'll try and keep it simple
and stay within their native functions. I'm still trying to incorporate
range however.

What I'm doing is building a model that will allow the user to input the
minimum and maximum possible values (between 0 and 1) and then have Excel
pull a random number from a normal or other distribution. Your suggestion of
=NORMSINV(RAND()) got me halfway there but I'm trying to figure out a way to
incorporate the range the user inputs. It could also be that I need to
assume some mean and standard deviation. =NORMSINV(RAND()) uses 0 and 1
respectively.

Any ideas or places to look?

Thanks

McGinty

 

[Guest]

=NORMSINV(RAND())*SD+mean
or equuivalently
=NORMINV(RAND(),mean,SD)

Similarly, you can theoretically use the inverse of any other distribution,
but all Excel inverses other than normal are inadequate to the task, so I
would recommend using Ian Smith's VBA functions.

Jerry

 

[Guest]

Thanks, that's very helpful and I'll give it a shot.

McGinty