"Ahmad" <adaha...@gmail.com> wrote in message news:6f28a95e-f0b8-4764...@n35g2000yqf.googlegroups.com...hi .
I try to generate normal distributed random number,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%.
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 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.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 285 |
Nodes: | 16 (2 / 14) |
Uptime: | 68:59:52 |
Calls: | 6,488 |
Calls today: | 1 |
Files: | 12,096 |
Messages: | 5,275,379 |