Random number based on mean and std dev

[excelbetter]

First off, I'm no statistics expert


I am trying to find random numbers based on knowing the mean and std deviation of a dataset.


Years ago, I recall using the formula of "=NormalValue" to do what I want, but I don't see that formula offered in my Excel and therefore don't know what to use now.


Let's assume the following...


Mean = 5.4


Std Dev = 8.3


Therefore, I am looking (I think) to generate random numbers based on the aforementioned. Considering 1 Std Dev is 8.3, I think all of the results should fall between either 5.4+8.3 (ie. 13.7) OR 5.4-8.3 (ie. -2.9)


Do you know what formula to use?

Thank you.

 

[AlanY]

this will work with 1 decimal places for mean/SD;

mean in B1 and SD in B2

=RANDBETWEEN((B1-B2)*10,(B1+B2)*10)/10

 

[Tetra201]

Please be aware that RANDBETWEEN returns a new random number every time the worksheet is calculated/opened.

 

[gsbelbin]

This will give data between the values given but the resulting data will not be normally distributed which I think is what is required. What you need to do is generate a uniform distribution of random numbers and then transform them to a normal distribution with the required mean and standard deviation. If you generate a column of random numbers using RAND() and then use the function NORM.INV that will do the job. NORM.INV takes 3 parameters, one is the random number generated previously and the other two are the required mean and standard deviation.

 

[joeu2004]

=NORMINV(RAND(), M1, S1)

where M1 contains the mean and S1 contains the std dev.