I am writing a computer lab for my Ecology class and want to make up data for them to practice plotting, stats, etc... I was told it is possible to generate data given the target mean, variance (st dev, std error, or CI), and sample size. Any suggestions? Thanks!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
generate data with known mean, variance, and sample size???
- Thread starter jswhite
- Start date
Re: generate data with known mean, variance, and sample size
Welcome to the board!
Unfortunatly I dont think its possible using Excel.
There could be several data samples that have the same mean, variance, samplesize and anyother stat. Excel would not be able to determine which sample you want.
It could however give you random data that you could use to calculate those numbers...
Welcome to the board!
Unfortunatly I dont think its possible using Excel.
There could be several data samples that have the same mean, variance, samplesize and anyother stat. Excel would not be able to determine which sample you want.
It could however give you random data that you could use to calculate those numbers...
Upvote
0
tusharm
MrExcel MVP
- Joined
- May 28, 2002
- Messages
- 11,029
Re: generate data with known mean, variance, and sample size
Use Tools | Data Analysis... | Random Number Generator.
That said, you can always generate your own random numbers with the following deceptively simple and absolutely generic method: Function-inverse(Rand())
So, to get 30 random numbers from a normal distribution with mean 10 and standard deviation 2, select 30 cells and array-enter =NORMINV(RAND(),10,2)
The above technique works for *any* continuous distribution. And, if XL supports the inverse function (as it does for F, T, Gamma, Normal, and LogNormal -- to name a few) all you need is the same technique used in the previous paragraph for the normal distribution.
The concept works for discrete functions also, but you have to do a bit extra work to calculate the inverse function value.
Obviously, the results are only as good as reliability and accuracy of the underlying XL functions.
Finally, to array-enter a formula, complete formula entry not with the ENTER key but with the combination CTRL+SHIFT+ENTER keys. If done correctly, XL will display the formula in braces { and }
Use Tools | Data Analysis... | Random Number Generator.
That said, you can always generate your own random numbers with the following deceptively simple and absolutely generic method: Function-inverse(Rand())
So, to get 30 random numbers from a normal distribution with mean 10 and standard deviation 2, select 30 cells and array-enter =NORMINV(RAND(),10,2)
The above technique works for *any* continuous distribution. And, if XL supports the inverse function (as it does for F, T, Gamma, Normal, and LogNormal -- to name a few) all you need is the same technique used in the previous paragraph for the normal distribution.
The concept works for discrete functions also, but you have to do a bit extra work to calculate the inverse function value.
Obviously, the results are only as good as reliability and accuracy of the underlying XL functions.
Finally, to array-enter a formula, complete formula entry not with the ENTER key but with the combination CTRL+SHIFT+ENTER keys. If done correctly, XL will display the formula in braces { and }
jswhite said:
Upvote
0
