View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
abdelkader bouchami abdelkader bouchami is offline
external usenet poster
 
Posts: 1
Default eliminate negative value from normal random number distribution

Le lundi 29 août 2011 Ã* 01:25:07 UTC+2, joeu2004 a écritÂ*:
"Ahmad" wrote in message
...
I try to generate normal distributed random number,
my problem how i can eliminate negative value, so i
used this function to generate random numbers
=(NORMSINV(RAND())*4)+6
where 4 is stdev, and 6 is average

If the mean is 6 and the std dev is 4, the normal distribution curve will
become negative to the left of -1.5sd, i.e. when RAND() is less than
NORMSDIST(-1.5) -- about 6.68%.
If your intent is to clip the normal distribution curve at zero on the left,
you can use:
=MAX(0,NORMINV(RAND(),6,4))
Note that NORMINV(RAND(),6,4) is the same as NORMSINV(RAND())*4+6.
If your intent is to shift the normal distribution curve to the right so
that the left tail is non-negative, the mean will no longer be 6.
Moreover, theoretically it cannot be done because the tails are infinitely
asymptotic.
However, in practice, it can be done either by determining the negative-most
return value from NORMSINV (-30 in XL2003), or by arbitrarily assigning zero
to a "large" negative z-score, e.g. -8sd, and clipping anything to the left
of that.
It is risky to rely on the negative-most return value from NORMSINV. I
presume it is not documented; ergo, it might change from release-to-release.

hi .
i need to function Norm.s.inv(rand()) on Exel. pleas.