Monday, April 05, 2010

How to Calculate a Random Number from a Normal Distribution?

The NORMINV(probability, mean, standard_dev) function in Microsoft Excel returns the value x such that, with probability p, a normal random variable with mean mu and standard deviation sigma takes on a value less than or equal to x. Using RAND() function that generates random probabilities between 0 and 1, we can use the NORMINV function to calculate a random number from a normal distribution, using this formula:

=NORMINV(RAND(), mean, standard_dev)

However, Jerry W. Lewis, a former Excel MVP and a professional statistician, commented that using NORMINV(RANDINV()...) to generate Normal variates is not recommended in Excel XP or earlier. Excel 2003 or later yields acceptable result.

Jerry recommends the Box-Muller Transformation method instead. Using Box-Muller Transformation approach to calculate a random number from a normal distribution, the formula in Excel would be

=SQRT(-2*LN(RAND()))*SIN(2*PI()*RAND())

No comments:

Post a Comment

Do provide your constructive comment. I appreciate that.