• eliminate negative value from normal random number distribution

    From abdelkader bouchami@21:1/5 to All on Fri Jul 24 02:49:59 2020
    Le lundi 29 août 2011 à 01:25:07 UTC+2, joeu2004 a écrit :
    "Ahmad" <adaha...@gmail.com> wrote in message news:6f28a95e-f0b8-4764...@n35g2000yqf.googlegroups.com...
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)