Business Statistics MATH 190 / BUSN 190 - Excel Assignment Chapter 8
The purpose of this assignment is to show you how statistics vary from sample to sample. The assignment in the book says to create your own data, but we are giving you data to start with. Start with this Excel file: Ch8_Confidence_Interval.xls
The file contains the height in inches of all 453 graduates of an Academy. So we already know the population mean and population standard deviation. For this assignment, generate 10 random samples (of size 40) from that population, and calculate 10 different 90% confidence intervals for the mean, to see if these 10 intervals contain the actual population mean.
Here are instructions:
Part A
Randomly choose a sample of 40 values from the population of 453 measurements. Do this 10 times. An easy way to do this is to assign each value a random number, then sort by that random number, then select the top 40 values.
Click in the cell A11, and type this formula: =RANDBETWEEN(1,10000) and hit Enter. Next, drag that formula all the way to the bottom of the list of data, so you get a bunch of random numbers.
Next, sort those three columns by selecting (highlighting) from cell A11 all the way down to C463, then sort by clicking the Sort Ascending (A-Z) button, or by choosing Data... Sort from the menu (Sort on Column A). This will randomly shuffle the 453 data values. Copy the top 40 height values into the column labeled Sample 1. Repeat this process until you have all 10 samples. (Be sure to check that your samples contain height data from column C, not the other identifying numbers from columns A and B!)
You should see 10 samples of 40 data values each... each representing heights of a graduate of Preteski Academy.
Part B
Construct a 90% confidence interval for the mean for each sample. First, calculate the sample mean (x-bar). Do this with the Excel average function. For example, for the first sample click in cell E6, then enter the formula =AVERAGE(E11:E50). Repeat this for all ten samples so that you have the average of each sample.
Next, calculate E with the Confidence function, which is the formula: =CONFIDENCE(0.1,C7,40). Type this formula into cell E7. This is what the values represent:
0.1 = 1 - c, c is the confidence level of 90% or 0.9
cell C7 is the standard deviation for the data. You should use this for all the samples.
40 is the sample size, n. Same for all samples.This formula has given you the Error value, E, for each sample.
Finally, calculate the confidence interval (xbar - E, xbar + E ) by adding and subtracting Efrom your average (x-bar). In cell E8 you would have =E6-E7 and in cell E9 you would have =E6+E7.
Do this for all 10 samples.
Part C
Answer the questions in the book. There is space for your answers in the Excel file under the samples.
Email your file to angela.j.loudon@biola.edu - if you have any questions, email me on BUBBS.