Search the Elsmar Cove!
**Search ALL of Elsmar.com** with DuckDuckGo Especially for content not in the forum
Such as files in the Cove "Members" Directory
Social Distancing - It's not just YOUR life - It's ALL of OUR lives!
Me <——————— 6 Feet ———————-> You

Random 'Normal Distribution' Numbers in Excel

M

M Greenaway

#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.
 

Atul Khandekar

Quite Involved in Discussions
#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.
 

Tim Folkerts

Super Moderator
#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
Staff member
Super Moderator
#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

Staff member
Admin
#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
Staff member
Super Moderator
#7
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

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

Steve Prevette

Deming Disciple
Staff member
Super Moderator
#9
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
Staff member
Super Moderator
#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.
 
Top Bottom