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
    Could you show a few plots you've generated?2012-05-04
  • 1
    Done. These are with 1000 numbers each.2012-05-04
  • 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?2012-05-04
  • 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