Random 'Normal Distribution' Numbers in Excel

M

M Greenaway

Hi All

Does anyone know how to generate random numbers in Excel that would follow a normal distribution ?

I am trying to simulate something and would like to simulate a fluctuating demand that is normally distributed when viewed as a whole.
 
A

Atul Khandekar

Re: Random 'Normal' Numbers in Excel

Have you tried the menu: Tools -> Data Analysis -> Random Number Generation ?

You'll require the Analysis Toolpack installed.
 

Tim Folkerts

Trusted Information Resource
I have heard in the past that excel doesn't do a great job generating random normal data. I don't know just what the problem may have been, and perhaps it has been fixed.

In any case, unless you are doing something really extensive and precise, it shouldn't matter.

Tim F
 

Steve Prevette

Deming Disciple
Leader
Super Moderator
Yes, rand() in Excel is an unknown quantity. I've never seen any documentation as to how it generates, and some random numbers generators have been very poor at short cycling and patterns within their digits. You can buy statistical grade random number generators, but unless you are doing a very critical simulation with millions of repetitions, rand() is probably good enough.
 

BradM

Leader
Admin
Correct my thought process here, if I'm wrong. Randomly generated numbers are one thing, randomly generating numbers that follow a normal distribution is another matter.

If I randomly generate 1 million numbers between 0-100, I should roughly have 10,000 1's, 10,000 2's, etc., so that the distribution would look more like a straight line.

Am I missing something?
 

Steve Prevette

Deming Disciple
Leader
Super Moderator
Correct my thought process here, if I'm wrong. Randomly generated numbers are one thing, randomly generating numbers that follow a normal distribution is another matter.

If I randomly generate 1 million numbers between 0-100, I should roughly have 10,000 1's, 10,000 2's, etc., so that the distribution would look more like a straight line.

Am I missing something?

Computer pseudo-random number generators (the technically correct term) generate out to as many decimal places as the program allows. For example, I just entered rand() in Excel and got the result:

0.681413954534054000000000000000

Which means the Excel generator goes out to about 15 decimal places. I believe that is "Double-precision" in the old computer vernacular.
 
B

Bill Ryan - 2007

But to answer Brad's question - will it be a "normal" distribution without any more queries?
 

Steve Prevette

Deming Disciple
Leader
Super Moderator
But to answer Brad's question - will it be a "normal" distribution without any more queries?

If you do what was earlier suggested by Darius - take the rand() function, which gives a 0 to 1 uniform random number, and run it through the inverse normal function, you'll get a normal distribution.
 

Statistical Steven

Statistician
Leader
Super Moderator
The RAND() function is Uniform(0,1) which has a mean of 0.5 and a standard deviation of 0.28. if you want to generate normal random variables you have to use the method proposed by Darius.
 
Top Bottom