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.29 another 4.77 .... any ideas?
Thanks
You are picking values from a distribution. The overall population has a mean and a distribution. Your sample may not match those values exactly.
Gary's Student
Sorry I'm not sure I understand exactly. Are you saying that you can't get the exact same mean and distribution so if it is a bit off the target it is still ok?
Say you have a population of 10,000 men. you have measured the height of each man and have calculated the average and standard deviation of the total group.
If you pick a subset of 10 men and calculate the average and standard deviation of the subset, the values are likely to be "close" to the values of the overall population.
What you are doing is realistic.
It is possible to "force the exact distribution, but this may not be what you need.
I am trying to get data with the exact mean and distribution as I am doing some data analysis. How can I do this I thought it was the formula in the first post but I guess it's not
See the attached workbook. A1 thru A10 were calculated from your formula. They were then "fixed" (PasteSpecialValues back onto the data) A11 & A12 are the formulas for average and stdev.
These cells were copied into B1 thru B12. B10 was adjusted, using Solver, to force the stdev to 2.9065
The values in B1 thru B10 were copied into C1 thru C10 with an adjustment factor added. The adjustment factor is cell D1.
The adjustment factor (D1) was adjusted, using Solver, to force the average to 5.55
As C11 & C12 indicate, the exact values are achieved.
Not sure why you'd need Solver:
B1: =AVERAGE(B$4:B$23)
Please Login or Register to view this content.
B2: =STDEV(B$5:B$23)
B4 and copied down: =NORMSINV(RAND())
C1, D1: Desired mean and SD
C4 and copied down gives the desired deviates: =(B4 - B$1) * C$2 / B$2 + C$1
C25 and C26 verify the result:
C25: =AVERAGE(C$4:C$23)
C26: =STDEV(C$5:C$23)
Entia non sunt multiplicanda sine necessitate
Nice Stephen!
Great thanks shgand to the others
Thank you, Gary, you're welcome, TheCase.
array formula
=STANDARDIZE(B4:B23,AVERAGE(B4:B23),STDEV(B4:B23)/2.9065)+5.55
How does that relate to the question?
Just the same as yours does Stephen, although without the typo and redundant separate calculation of average and StDev that you use.Originally Posted by shg
Have you tried running it? Do so and see. Or does it require further explanation?
Last edited by kalak; 03-22-2013 at 05:04 AM.
Don't think so. Your formula, in col D below, gives the desired mean but not the SD.Just the same as yours does Stephen, ...
Please Login or Register to view this content.So I reckon it does...Or does it require further explanation?
Last edited by shg; 03-22-2013 at 03:46 PM.
The further explanation would be better coming from you.
Specifically why, from your above data, you reported the kalak StDev from the range D5:D23 when your data clearly require it to be D4:D23.
Would you like to redo your calculation from my formula, but using the correct range this time, and report back the result.
Do you still assert your view that "Your formula, in col D below, gives the desired mean but not the SD."?
Incidentally, that array version could, if anyone wanted, easily be converted to a straight fill-down version by converting the range to absolute rather than relative.
Are you now happy that my answer above does relate to the opening question?
kalak & shg:
Both of your equations work to perfection. See the attached.
kalak,
You are absolutely correct, please accept my apology.
I prefer my solution, but yours is certainly equivalent.
hey buddy,
no need for any apologies. exchange of ideas is always useful and perhaps we've both learned something.
anyone can make a slip-up, and I guess most of us do at times.
for what it's worth, I prefer my own approach to this problem, which is why I posted it in a thread that had already been well answered. one-line formula that does the whole job rather than doing it in several stages suits my way of thinking somewhat better. but each to their tastes.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks