random number
 

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

random number
 

" wrote:

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

random number
 

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/micro...4626d90261b42f

An even better algorithm is the Mersenne Twister
http://www.math.sci.hiroshima-u.ac.j...at/MT/emt.html
http://www-personal.engin.umich.edu/...neTwister.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

"PeterAtherton" wrote:



" wrote:

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

random number
 

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



" wrote:

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

random number
 

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

B. R.Ramachandran wrote:

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



" wrote:


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