LAB 7 FOR EXCEL 2007 SAMPLING DISTRIBUTIONS OF STATISTICS NAME LAB TIME PHYS ROOM NO 1 2 3 Every statistic such as the Sample Mean the Sample Standard Deviation the Sample Variance has a SAMPLING DISTRIBUTION which shows the pattern of variation of the statistic when the same population is sampled over and over and over In this lab we will use Excel s random number generator to draw 2500 observations from a population which has a Normal Distribution with a mean 100 and a standard deviation 5 What should be the approximate values of the smallest and largest of these 2500 Use and 3 sigma as the approximate edge of the normal distribution Minimum should be approximately mean 3 std dev 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Maximum should be approximately mean 3 std dev In Excel do Data Data Analysis Random Number Generator In the window which appears set Variables 5 Number of Random Numbers 500 Distribution Normal Mean 100 Standard Deviation 5 Random Seed 25674 click on Output Range and enter A2 in the box beside the output range Check that the random seed has not changed and then click OK On line 1 of Excel number the columns A through E as 1 2 3 4 5 We now have 500 lines of 5 observations each in Columns A through E We will treat each line as a random sample of size 5 So we have 500 random samples of size 5 in rows 2 through 501 CALCULATION OF MEAN STANDARD DEVIATION AND RANGE FOR EACH SAMPLE In G1 enter sample mean in H1 enter sample std dev in I1 enter sample max in J1 enter sample min in K1 enter sample range In G2 calculate the mean for a2 e2 Use the excel AVERAGE a2 e2 In H2 calculate the standard deviation of a2 e2 Use excel STDEV a2 e2 In I2 calculate the maximum for a2 e2 Use excel MAX a2 e2 In J2 calculate the minimum for a2 e2 Use excel MIN a2 e2 In K2 calculate the sample range I2 J2 Copy G2 K2 down through line 501 In G503 calculate the overall mean of the 500 sample means Use excel AVERGE g2 g501 Enter the overall mean of the 500 sample means Assuming that the POPULATION MEAN 100 calculate the percent error for the mean of the 500 sample means using G503 100 100 100 Does the sample mean appear to be an unbiased statistic In G504 calculate the standard deviation of the 500 sample means Use excel STDEV g2 g501 22 Enter the standard deviation of the 500 sample means 23 Theory says the standard deviation of the sample means Population std dev sqrt n or 5 sqrt 5 2 236 24 Calculate the percent error for the value in step 22 vs the theoretical value in step 23 using G504 2 236 2 236 100 25 In H 503 calculate the mean of the 500 sample standard deviations 26 Assuming that the POPULATION STANDARD DEVIATION 5 calculate the percent error in the mean of the 500 sample standard deviations 27 Does the sample standard deviation appear to be an unbiased statistic 28 Theory says that the sample standard deviation will average 6 lower than the population standard deviation when the sample size 5 If this is true the mean of the 500 sample standard deviations should have been 4 7000 Calculate the percent error between the actual mean of the 500 sample standard deviations step 25 and the predicted value given in step 28 29 30 In K503 calculate the mean of the 500 sample ranges 31 Theory says that the sample range for a sample size 5 will average 2 326 times the population standard deviation or 5 2 326 11 63 32 Calculate the percent error for the actual mean of 500 sample ranges step 30 vs the theoretical value step 31 33 34 35 36 37 38 39 DRAWING A HISTOGRAM OF THE SAMPLE MEANS Do Tools Data Analysis Histogram and click OK Input range G2 G501 Click on Output Range and in the box enter M1 Click on Chart Output and click OK Close up the spaces between the histogram bars right click on any bar click on Format Data Series click on Options and bring Gap down to 0 Size the histogram Correct the title to read Histogram Of Sample Means 40 Comment on the shape of the histogram 41 Print the histogram to turn in with these answers