Random 'Normal Distribution' Numbers in Excel

M

M Greenaway

• Jun 21, 2007

• #1

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

• Jun 21, 2007

• #2

Re: Random 'Normal' Numbers in Excel

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

You'll require the Analysis Toolpack installed.

 

D

Darius

• Jun 21, 2007

• #3

Just use NORMINV(RAND(),$B$1,$B$2)

Being $B$1 = Mean, $B$2 = stdev

 

Tim Folkerts

Trusted Information Resource

• Jun 21, 2007

• #4

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

• Jun 21, 2007

• #5

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

• Jun 21, 2007

• #6

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

• Jun 21, 2007

• #7

BradM said:

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?

Click to expand...

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

• Jun 21, 2007

• #8

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

 

Steve Prevette

Deming Disciple

Leader

Super Moderator

• Jun 21, 2007

• #9

Bill Ryan said:

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

Click to expand...

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

• Jun 21, 2007

• #10

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.