I have created 95% confidence interval estimates of the population mean µ from your data in columns E and F. I have used the fact that I know σ is 6.5, that the sample was from a normal population, and that you all took samples of size 10. Therefore, the interval becomes sample mean ± 1.96*6.5/3.16. (3.16 is the approximate square root of 10.) I have higlighted the one interval that did NOT cover the true (known only to me) value of µ, which is 76.06.
Here is your assignment:
In columns H and I generate 80% confidence intervals for µ, in a manner similar to the way I generated the 95% intervals. Indicate those which do not contain the true value of the mean in some way.
In columns K and L, generate 95% confidence intervals based on the t statistic. and in columns N and O, generate 80% intervals based on the t statstics. Again, indicate in some way the intervals that fail to include the (true) mean.
We have also said that the (collection of all) sample means, should have a mean itself that is the same as the population mean and a standard deviation that is equal to the population standard deviation divided by the square root of the sample size. That implies that the mean of all sample means should be about 76.06 and the standard deviation about 6.5/(square root 10). Use the statistical capabilities of Excel to find the mean and standard deviation of our sample means (all the data in column B) and compare them to these values. Are they close?
This solution contains calculations to determine the mean and standard deviation as well as the estimated mean and standard deviation in an Excel file.