random number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
[email protected] said:
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
Click to expand...

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
 
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
 
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
 
Back
Top