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

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.

"McGinty" wrote:

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

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

"McGinty" wrote:

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

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

"Office Helper" wrote:

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.

"McGinty" wrote:

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

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


"Jerry W. Lewis" wrote:

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

"McGinty" wrote:

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

=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

"McGinty" wrote:

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


"Jerry W. Lewis" wrote:

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

"McGinty" wrote:

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

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

McGinty

"Jerry W. Lewis" wrote:

=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

"McGinty" wrote:

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


"Jerry W. Lewis" wrote:

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

"McGinty" wrote:

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