3
$\begingroup$

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

  • 0
    What is `NORMSINV` documented to do? I suppose `RAND` returns a uniformly likely value in $[0,1)$?2012-05-04

1 Answers 1

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.

enter image description here

  • 0
    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!2012-05-04