Remember Me? August 28th 11, 10:02 PM posted to microsoft.public.excel.worksheet.functions
 Ahmad external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 23 eliminate negative value from normal random number distribution

hi
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

thanks August 29th 11, 12:25 AM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829 eliminate negative value from normal random number distribution

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

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. August 29th 11, 12:54 AM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829 eliminate negative value from normal random number distribution

PS....

I 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

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. August 29th 11, 05:45 PM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829 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. August 29th 11, 07:56 PM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster First recorded activity by ExcelBanter: Jul 2011 Posts: 829 eliminate negative value from normal random number distribution

PS....

I wrote:
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%.

I should have noted that -1.5sd depends on the mean and std dev. More
generally, the normal distribution becomes negative to the left of -mean/sd;
e.g. -6/4. July 24th 20, 10:49 AM posted to microsoft.public.excel.worksheet.functions
 abdelkader bouchami external usenet poster First recorded activity by ExcelBanter: Jul 2020 Posts: 1 eliminate negative value from normal random number distribution

Le lundi 29 aoÃ»t 2011 Ã* 01:25:07 UTC+2, joeu2004 a Ã©critÂ*:
...
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
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.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Alexandra Lopes Excel Worksheet Functions 2 December 4th 08 03:36 PM McGinty Excel Worksheet Functions 6 October 26th 06 02:57 PM gatosonreyendo Excel Discussion (Misc queries) 3 December 12th 05 05:04 AM FLKULCHAR Excel Worksheet Functions 13 October 27th 05 10:35 PM darebo Excel Discussion (Misc queries) 2 December 6th 04 06:01 PM

All times are GMT +1. The time now is 01:48 PM. Copyright ©2004-2020 ExcelBanter.