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?
Why doesn't NORMSINV(RAND()) in Excel work as a standard normal random number generator?
3
$\begingroup$
random
normal-distribution
-
0Could you show a few plots you've generated? – 2012-05-04
-
1Done. These are with 1000 numbers each. – 2012-05-04
-
2These 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? – 2012-05-04
-
0What is `NORMSINV` documented to do? I suppose `RAND` returns a uniformly likely value in $[0,1)$? – 2012-05-04
1 Answers
10
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.
-
0Duh... 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! – 2012-05-04