RE: random number

PeterAtherton

unread,

Nov 22, 2005, 4:19:09 PM11/22/05

to

> using betadist and normdist only referencesa value from the distribution
> how do you create a random variable that comes from these distributions
> instead of rand which is uniform

You need to have installed the Analysis Tool Pack, Tools, Add-Ins Click the
Analysis Tool Pack CheckBox.

The choose, Tools, Data Analysis, And select the Generate Randon Numbers. To
create a Normal Distribution, you will select the Number of columns and the
total numbers you want to generate. Then you will have to enter the mean, and
standard Distribution. You can generate the numbers on a new sheet

Regards
Peter

Jerry W. Lewis

unread,

Nov 22, 2005, 6:07:04 PM11/22/05

to

The approach used by ATP is
=BETAINV(RAND(),a,b)
=NORMINV(RAND(),m,s)
This approach is is mathematically correct, but numerically tends to magnify
the faults of the uniform random number generator and the inverse
distribution functions that are used. ATP uses a different (worse) random
number generator than Excel, so I suggest avoiding it for serious work.

If you do not have Excel 2003, I suggest implementing its new (greatly
improved) random number generator
http://support.microsoft.com/kb/q828795/
Ian Smith has implemented it in VBA

http://groups.google.com/group/microsoft.public.excel.programming/msg/3c4626d90261b42f

An even better algorithm is the Mersenne Twister
http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/emt.html
http://www-personal.engin.umich.edu/~wagnerr/MersenneTwister.html
which is implemented in the freeware NtRand
http://www.numtech.com/NtRand/

For inverse distribution functions, I would recommend Ian Smith's VBA library
http://members.aol.com/iandjmsmith/Examples.xls
even if you do have Excel 2003.

Jerry

Jerry W. Lewis

unread,

Nov 22, 2005, 6:12:06 PM11/22/05

to

I think you meand NORMINV and BETAINV. See my reply under the OP's repeat of
this question.

It is generally better to reply to your original thread with followup
issues, since it keeps the discussion together and ensures you don't lose
people who are already trying to help you.

Jerry

"Gary''s Student" wrote:

> Use NORMDIST and BETADIST
> --
> Gary's Student
>
>
> "ag...@kent.ac.uk" wrote:
>
> > how can i create a normally distributed random number
> > and a random number that has a beta distribution
> > i need these both to be integer outputs
> > thanks for the help

B. R.Ramachandran

unread,

Nov 22, 2005, 6:33:03 PM11/22/05

to

Hi,

You can generate a pseudonormal random number with a mean of 0 and standard
deviation of 1 with one of the following formulas (Box-Muller transformation)
from two uniformaly distributed random numbers (between 0 and 1)

=SQRT(-2*LN(RAND()))*COS(2*PI()*RAND())

or

=SQRT(-2*LN(RAND()))*SIN(2*PI()*RAND())

The Random Number Generator in the Analysis Toolpack in Excel also does the
job as suggested by PeterAtherton.

Regards,
B. R. Ramachandran

Jerry W. Lewis

unread,

Nov 22, 2005, 10:33:54 PM11/22/05

to

This approach is known as the Box-Muller method
http://www.taygeta.com/random/gaussian.html
It is also mathematically exact, and Excel's implementation of LN(),
COS(), and SIN() are to machine accuracy, but my concerns about pre-2003
RAND() still apply.

Jerry