PS....

I wrote:
"Ahmad" wrote:
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))

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))

The MAX(0,...) should be superfluous. I added it to accommodate any
floating-point anomalies that might result in less than zero. In XL2003,
NORMINV(NORMSDIST(-1.5),6,4) results in about -1.77636E-15, even though it
should be exactly zero.

Theoretically, NORMINV might return a #NUM error if the first parameter
(probability) exceeds some internal limit. In XL2003, that is only for a
probability of exactly 100%[*]. In the expression above, that would mean
that RAND() is exactly 1, which should not happen [**].

-----[*] I tried NORMINV(1-2^-53,6,4) in XL2003, and that works. You might try
that in your version of Excel. Note that 1-2^-53 is the largest decimal
fraction less than 1 that can be represented in Excel.

[**] Theoretically, floating-point anomalies might cause the arithmetic to
result in exactly 1 even if RAND() is not. I don't know if that might be a
problem in actual practice. The largest possible result of RAND() in XL2003
is "very far" from 1. YMMV in later Excel versions, especially XL2010,
which redesigned RAND(). Nonetheless, I don't think it is worth it to
bullet-proof the formula on the high end.