Normal Random Variable Generator

[Ming Shao]

Hi

I am working on a simulation program. How can I use a normal random
variable generator in my VBA code in Excel? There is a function NORMINV
in Excel, but I don't know how to use in my VBA code. The compiler
doesn't know this function. Please help.

Ming

(e-mail address removed)

 

[Bob Phillips]

?worksheetfunction.NORMINV(0.908789,40,1.5)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ludwig]

That functon will give you the inverse of the normal probability
density function, not a random normal variable. You can create quasi
random variables in Excel :

Obtain 12 independent samples of rand()-6 and add them toguether.
Rand() gives you a random number between 0 and 1 with from a uniform
distribution.

for x =1 to 12

y=y+application.rnd()

next x

y=y-6
The end result will be a random number with a mean of 0 and a stdev of
1.

this will be a close approximation but fourth and hihger orders may
differ from a normal distribution.




There are other methods top generate more accurate normal random
numbers such as the box mueller

function BM()
randomize
do
x=2*rnd() -1
y=2*rnd() -1
dist= x*x+y*y
loop until dist<1

BM= x*sqr(-2*log(dist)/dist)
end function

cheers.

 

[Tushar Mehta]

Depending on how far you trust the various functions involved, NORMSINV
(RND()) will generate normally distributed random values. Just as any
{x}Inv(RND()) will generate a {x}-distributed random variable.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions