I wrote the following BASIC code years ago and it works great.
My code is based on an article in Byte Magazine, October 1985,
Page 137 "Simulating the Normal Distribution"
The heart of the article is contained in one sentence:
"The standard deviation of the means of N items from a
from a uniform distribution is given by:
Standard Deviation = 1/SQR(12*N)
Here is my function which uses the sum of
ten random numbers...
Public Function NormalNum(MN, SD) As Single
'returns a number from normal distribution
'centered at mean MN with standard deviation SD
Dim X As Single
X = (Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd + Rnd)
X = X / 10 - 0.5
X = X * SD * Sqr(120) + MN
NormalNum = X
End Function
This function could easily be entered as an
Excel user defined function using VBA.
Doug McCormack
CorMac Technologies Inc.
cormactech.com
MJ Wang wrote:
> Try NORMSINV(RAND()) to generate standard normal random numbers. The principle
> is the following:
>
> Assume X is a continuous uniform random variable in [0,1], and Y has a
> probability distribution function F, which has inverse function F_inverse.
>
> Let Z=F_inverse(X)(in your case, Z=NORMSINV(RAND())), and t is a number in
> [0,1], then
> Prob(Z<t)=prob(F_inverse(X)<t)=prob(X<F(t))=F(t)/(1-0)=F(t).
> So, Z is a random variable with a probability distribution function F. That is,
> in your case, Z has a standard normal distribution.
>
> According to this principle, you can generate other different random numbers as
> long as you can find the inverse probability distribution function of it.
>
> Jay
>
> Ronny Richardson wrote:
> >
> > Can anyone suggest a simple way to generate normal random numbers in Excel?
> > In my specific case, I want to use them as an input to a small simulation.
> > Thanks.
> >
> > Dr. Ronny Richardson
> > Associate Professor of Management
> > Southern Polytechnic State University
> > School of Management
> > 1100 South Marietta Parkway
> > Marietta, GA 30060-2896
> >
> > Phone: (770) 528-5542
> > Fax: (770) 528-4967
