Home 
Search 
Today's Posts 
#1




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 
#2




eliminate negative value from normal random number distribution
"Ahmad" wrote in message
... 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 nonnegative, 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 negativemost return value from NORMSINV (30 in XL2003), or by arbitrarily assigning zero to a "large" negative zscore, e.g. 8sd, and clipping anything to the left of that. It is risky to rely on the negativemost return value from NORMSINV. I presume it is not documented; ergo, it might change from releasetorelease. 
#3




eliminate negative value from normal random number distribution
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 floatingpoint anomalies that might result in less than zero. In XL2003, NORMINV(NORMSDIST(1.5),6,4) results in about 1.77636E15, 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(12^53,6,4) in XL2003, and that works. You might try that in your version of Excel. Note that 12^53 is the largest decimal fraction less than 1 that can be represented in Excel. [**] Theoretically, floatingpoint 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 bulletproof the formula on the high end. 
#4




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, floatingpoint 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 bulletproof the formula on the high end. To be consistent, I guess it is also not worth it to bulletproof on the low end, as I did. The following is probably sufficient: =NORMINV(NORMSDIST(1.5)+(1NORMSDIST(1.5))*RAND(),6,4) Explanation.... The fact that NORMINV(NORMSDIST(1.5),6,4) results in about 1.77636E15 is a floatingpoint 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 floatingpoint anomalies. But RAND() would have to be infinitesimally close to zero. Again, the probability is very small. 
#5




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




eliminate negative value from normal random number distribution
Le lundi 29 aoÃ»t 2011 Ã* 01:25:07 UTC+2, joeu2004 a Ã©critÂ*:
"Ahmad" wrote in message ... 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 nonnegative, 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 negativemost return value from NORMSINV (30 in XL2003), or by arbitrarily assigning zero to a "large" negative zscore, e.g. 8sd, and clipping anything to the left of that. It is risky to rely on the negativemost return value from NORMSINV. I presume it is not documented; ergo, it might change from releasetorelease. hi . i need to function Norm.s.inv(rand()) on Exel. pleas. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Normal distribution  Excel Worksheet Functions  
Help picking a random number from a given distribution  Excel Worksheet Functions  
draw random from normal distribution  Excel Discussion (Misc queries)  
NORMAL DISTRIBUTION  Excel Worksheet Functions  
Seed numbers for random number generation, uniform distribution  Excel Discussion (Misc queries) 