eliminate negative value from normal random number distribution
Clarification....
I wrote:
If your intent is to clip the normal distribution curve,
but you want all random values to land in the unclipped
portion of the curve, then ostensibly you want:
=MAX(0,NORMINV(NORMSDIST(-1.5)+(100%-NORMSDIST(-1.5))*RAND(),6,4))
[....]
[**] Theoretically, floating-point anomalies might cause
the arithmetic to result in exactly 1 even if RAND() is not.
[....]
[So,] I don't think it is worth it to bullet-proof the
formula on the high end.
To be consistent, I guess it is also not worth it to bullet-proof on the low
end, as I did. The following is probably sufficient:
=NORMINV(NORMSDIST(-1.5)+(1-NORMSDIST(-1.5))*RAND(),6,4)
Explanation....
The fact that NORMINV(NORMSDIST(-1.5),6,4) results in about -1.77636E-15 is
a floating-point anomaly. In fact, it might be different in XL2010, perhaps
even XL2007. Note that NORMINV(NORMSDIST(-1.5)+2^-56,6,4) returns a
positive result. 2^-56 is the smallest possible increment for the result of
NORMSDIST(-1.5).
Theoretically, the RAND expression would result in NORMSDIST(-1.5) only when
RAND() is exactly zero[*]. In XL2003 and XL2007, the RAND algorithm never
returns exactly zero. I don't know about XL2010. In any case, the
probability is so very small that it probably is not worth the trouble.
-----[*] In actual practice, the RAND expression might result in NORMSDIST(-1.5)
even if RAND() is not exactly zero due to floating-point anomalies. But
RAND() would have to be infinitesimally close to zero. Again, the
probability is very small.
|