Generate random numbers with specific distribution with Excel

Question

I want to create 100 random numbers with a normal distribution (with μ = 10, σ = 7), and then draw a quantitative chart for these numbers.

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

One more question:

When I create, for example, 20 random numbers with RANDBETWEEN(Bottom,Top) , the numbers change every time the sheet is recounted. How can i avoid this?

Answer 1

Use the NORMINV function together with RAND() :

 =NORMINV(RAND(),10,7) 

To change the set of random values, select all the values, copy them and paste the (special) values ​​back into the same range.

---

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

Answer 2

As stated in the comment above (and not in the answer, so I am adding this), the Analysis Pack has a random number generator to generate a set of numbers. A good summary link is at http://www.bettersolutions.com/excel/EUN147/YI231420881.htm .

Answer 3

If you have Excel 2007, you can use

 =NORMSINV(RAND())*SD+MEAN 

Since in 2010 there were big changes in excel function

Answer 4

Take prey on a Wikipedia article on random numbers , as it talks about using sampling methods. You can find the equation for your normal distribution by connecting to this one

(equation via Wikipedia)

Regarding the second problem, go to “Options” under the “Circle” icon, go to the formulas and change the calculation to “Guide”. This will maintain your sheet and not recount the formulas every time.

Answer 5

About recount:

You can change your set of random values ​​each time you do the tuning by setting automatic recalculation to: manual recalculate . (Re) calculations are only performed when you press F9. Or shift F9.

Check out this link (albeit for an older version of excel than in the current 2013), for some information on this: https://support.office.com/en-us/article/Change-formula-recalculation-iteration-or- precision-73fc7dac-91cf-4d36-86e8-67124f6bcce4 .

Answer 6

Rand () generates an even distribution of random numbers between 0 and 1, but the norminv (or norm.inv) function accepts a uniform distribution of Rand () as an input to generate a normally distributed set of samples.

Answer 7

Numbers Generated

 =NORMINV(RAND(),10,7) 

are evenly distributed. If you want the numbers to be normally distributed, you have to write a function, I think.