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