CODEWITHSUNDEEP
excelrandomnormal-distribution
ha.M.ed
ha.M.ed

Reputation: 985

Generating random numbers with normal distribution in Excel

I want to produce 100 random numbers with normal distribution (with µ=10, σ=7) and then draw a quantity diagram for these numbers.

How can I produce random numbers with a specific distribution in Excel 2010?

One more question:

When I produce, for example, 20 random numbers with RANDBETWEEN(Bottom,Top), the numbers change every time the sheet recalculates. How can I keep this from happening?

Upvotes: 71

Views: 240295

Answers (8)

jonsca
jonsca

Reputation: 10381

Take a look at the Wikipedia article on random numbers as it talks about using sampling techniques. You can find the equation for your normal distribution by plugging into this one

pdf for normal distro

(equation via Wikipedia)

As for the second issue, go into Options under the circle Office icon, go to formulas, and change calculations to "Manual". That will maintain your sheet and not recalculate the formulas each time.

Upvotes: 1

datacoder
datacoder

Reputation: 66

Another interesting way to do this is using the Box-Muller Method. This lets you generate a normal distribution with mean of 0 and standard deviation σ (or variance σ2) of 1 using two uniform random distributions between 0 and 1. Then you can take this Norm(0,1) distribution and scale it to whatever mean and standard deviation you want.

Here's the formula in excel for a normal(0, 1) distribution:

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

Then use this formula to scale your normal distribution to mean 10 and standard deviation of 7:

Norm(µ=b, σ=a) = a*Norm(µ=0, σ2=1) + b

This would make the equation in Excel:

=7* SQRT(-2*LN( RAND()))*COS(2 * PI()*RAND()) + 10

You can read more about the math behind this Box-Muller Equation on en.Wikipedia

Note that this equation only works if you calculate the cosine function using radians.

Upvotes: 0

studgeek
studgeek

Reputation: 14920

As @osknows said in a comment above (rather than an answer which is why I am adding this), the Analysis Pack includes Random Number Generation functions (e.g. NORM.DIST, NORM.INV) to generate a set of numbers. A good summary link is at http://www.bettersolutions.com/excel/EUN147/YI231420881.htm.

Upvotes: 5

Excellll
Excellll

Reputation: 5785

Use the NORMINV function together with RAND():

=NORMINV(RAND(),10,7)

To keep your set of random values from changing, select all the values, copy them, and then paste (special) the values back into the same range.

Sample output (column A), 500 numbers generated with this formula:

enter image description here

Upvotes: 118

Matthew Murphy
Matthew Murphy

Reputation: 21

Rand() does generate a uniform distribution of random numbers between 0 and 1, but the norminv (or norm.inv) function is taking the uniform distributed Rand() as an input to generate the normally distributed sample set.

Upvotes: 2

Smit
Smit

Reputation: 65

The numbers generated by

=NORMINV(RAND(),10,7)

are uniformally distributed. If you want the numbers to be normally distributed, you will have to write a function I guess.

Upvotes: -6

cybork
cybork

Reputation: 587

About the recalculation:

You can keep your set of random values from changing every time you make an adjustment, by adjusting the automatic recalculation, to: manual recalculate. (Re)calculations are then only done when you press F9. Or shift F9.

See this link (though for older excel version than the current 2013) for some info about it: https://support.office.com/en-us/article/Change-formula-recalculation-iteration-or-precision-73fc7dac-91cf-4d36-86e8-67124f6bcce4.

Upvotes: 1

JeeHyoung Kim
JeeHyoung Kim

Reputation: 91

IF you have excel 2007, you can use

=NORMSINV(RAND())*SD+MEAN

Because there was a big change in 2010 about excel's function

Upvotes: 9

Related Questions