Normal Random Variable Generator

  • Thread starter Thread starter Ming Shao
  • Start date Start date
M

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)
 
?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)
 
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.
 
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
 
Back
Top