Random Numbers in a certain mean and standard deviation

[TheCase]

Hi,

I need to generate 20 random numbers that have the mean of 5.55 and standard deviation of 2.9065 I used this

=NORMINV(RAND(),5.55,2.9065)

Although everytime I refresh, F9, it the numbers don't fit the target mean or standard deviation it keeps changing for example one set of data might fit the mean of 5 another 4.1 .... any ideas?



Thanks

 

[mirabeau]

Hi,

I need to generate 20 random numbers that have the mean of 5.55 and standard deviation of 2.9065 I used this

=NORMINV(RAND(),5.55,2.9065)

Although everytime I refresh, F9, it the numbers don't fit the target mean or standard deviation it keeps changing for example one set of data might fit the mean of 5 another 4.1 .... any ideas?

Thanks

20 numbers with two conditions implies you have only (at most) 18 freely variable numbers.

If you like doing algebra you could generate 18 of them, and calculate the remaining two from the Average and StDev formulae.


If no real solution to that, and there may not be given that one of the conditions is nonlinear, then generate 17 and calculate the remaining 3.




But much easier to just use the Excel Solver.


Generate your 20 numbers. Calculate the Average and StDev in a couple of cells.

In Solver, put (cell containing) Average equal to 5.55 subject to the constraint that (cell containing) StDev equals 2.9065 by changing (preferably) all 20 cells (although fewer if you like).

Given the nonlinearity of the Standard deviation condition, the distribution of numbers you finish with won't be strictly the same as that generating your initial 20 numbers. I doubt this would be a problem for you, but I don't think there's a way around it.

 

[Firefly2012]

It would be useful if you could quote the cross-post at Excel Forum that you have made because shg and Gary's Student have both provided you with fairly comprehensive responses.

 

[mirabeau]

It would be useful if you could quote the cross-post at Excel Forum that you have made because shg and Gary's Student have both provided you with fairly comprehensive responses.

Would you care to provide a specific reference to enlighten those (like me) who know nothing about Excel Forum?

 

[sagyer]

*deleted*

 

[Andrew Poulsom]

Here's the link, which I got by Googling:

Random Numbers in a certain mean and standard deviation