Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

I am looking for an easy way to generate random numbers from a standard normal distribution in Excel. I realize the best way is probably the Box–Muller method, SQRT(-2*LOG(RAND()))*COS(2*PI()*RAND()), and indeed, 10,000 of these looks like a good standard normal distribution. However, I can't figure out why a simpler function, NORMSINV(RAND()), would not work as well. The distribution looks much flatter. Can somebody explain this to me?

enter image description here

share|improve this question
    
Could you show a few plots you've generated? –  J. M. May 4 '12 at 5:11
1  
Done. These are with 1000 numbers each. –  Craig W May 4 '12 at 5:16
2  
These both look like normal distributions with different variance. In fact, by eyeballing it I'd say it's the red one that has unit variance, while the blue one's variance is too low. Can you compute the variance for both distributions and check? –  Rahul May 4 '12 at 5:40
    
What is NORMSINV documented to do? I suppose RAND returns a uniformly likely value in $[0,1)$? –  MJD May 4 '12 at 6:52
add comment

1 Answer

up vote 9 down vote accepted

The problem not with NORMSINV, it is with your implementation of the Box-Muller transform. Excel's LOG function returns the base-$10$ logarithm, whereas what you need is the natural logarithm, LN.

Below is a histogram showing the Box-Muller transform using $\ln$ in blue, and the incorrect transform using $\log_{10}$ in red, with the expected curve from the standard normal distribution overlaid in dark blue.

enter image description here

share|improve this answer
    
Duh... The thought that the distribution simply had a different variance crossed my mind but for some reason I was convinced the NORMSINV(RAND()) distribution was not Gaussian. Optical illusion or something I guess...thanks! –  Craig W May 4 '12 at 16:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.