LAB 9 INSTRUCTIONS FOR EXCEL 2003 CONFIDENCE INTERVAL FOR THE POPULATION MEAN NAME LAB TIME LAB BLDG In this lab we will examine how confidence intervals for the population mean are calculated how often they are correct and the factors that affect the interval s width GENERATE 100 RANDOM SAMPLES OF SIZE 20 AND CALCULATE A 95 CONFIDENCE INTERVAL BASED ON EACH SAMPLE 1 Open an Excel file 2 Do Tools Data Analysis Random Number Generation OK 3 Number of variables 20 Number of Random Numbers 100 4 Distribution Normal Mean 10000 Std Dev 1000 5 Skip Random Seed Click on Output Range Enter A2 in window 6 Go back to Random Seed and enter 16892 Click OK 7 You should get random numbers in A2 T101 8 In V1 enter Sample Mean In W1 enter Lower Confidence Limit In X1 enter Upper Confidence Limit In Z1 enter 0 true 1 false 9 In V2 calculate the mean for A2 T2 10 In W2 calculate the lower confidence limit V2 1 96 1000 sqrt 20 11 In X2 calculate the upper confidence limit V2 1 96 1000 sqrt 20 12 In Z2 enter logic to determine whether each interval includes the true population mean of 10000 get the IF function get the window and for Logical Test use W2 10000 Go down to Value if false 1 Go up to Value if true get another IF function by clicking on IF in the tool bar at the left edge In the new window Logical Test use X2 10000 Value if true 0 Value if false 1 Click OK You should get a 0 since the interval on line 2 does include 10000 13 Copy the formulas in V2 Z2 down through line 101 14 In Z104 calculate the sum for Z2 Z101 This gives the number of intervals which were incorrect out of the 100 applications of the confidence interval formula Number of intervals incorrect number correct EXAMINE THE EFFECT OF SAMPLE SIZE AND CONFIDENCE LEVEL ON THE WIDTH OF THE CONFIDENCE INTERVAL 15 To examine the effect of sample size on the width of a confidence interval three samples of size n 20 n 80 and n 320 are to be taken from a population Lets pretend that from previous studies we know the standard deviation of the population 1000 16 Lets also pretend that the mean for each sample 10000 We are just doing this to simplify the calculations The actual sample means do not matter when we are calculating the width of the confidence interval 17 Using the formula for the confidence interval calculate nine intervals using sample sizes of 20 80 and 320 and confidence levels of 90 95 and 99 You can set this up in excel as follows 1 2 3 4 5 6 7 8 9 10 11 On line 110 in col B enter conf level in col C enter z in col D enter sample size in col E enter sample mean in col F enter lower conf limit in col G enter upper conf limit in col H enter width On line 111 in col B enter 90 in col C enter 1 645 in col D enter 20 in col E enter 10000 In F111 enter E111 C111 1000 SQRT D111 to calculate Lower Confidence Limit In G111 enter E111 C111 1000 SQRT D111 to calculate Upper Confidence Limit In H111 enter G111 F111 to calculate Width of Confidence Interval On line 112 copy line 111 down and then change the sample size in col D to 80 On line 113 copy line 112 down and then change the sample size in col D to 320 Set up lines 114 115 and 116 to be the same as lines 111 112 and 113 except use a confidence of 95 in col B and a value of 1 960 in col C Set up lines 117 118 and 119 to be the same as lines 114 115 and 116 except use a confidence of 99 in col B and a value of 2 576 in col C Format the widths in H111 H119 to 1 decimal place In the table below enter the confidence widths from col H WIDTH OF Sample Size 20 Confidence Level 90 H111 95 H114 99 H117 18 CONFIDENCE INTERVAL 80 320 H112 H115 H118 H113 H116 H119 By comparing widths in the above table what is the effect of changing sample size while holding confidence level constant Holding confidence level constant as sample size is increased the width of the confidence interval 19 20 When the sample size is quadrupled what is the ratio of the new width to the old width Does this ratio hold regardless of confidence level 21 Holding sample size constant as confidence level is changed from 90 to 95 to 99 the width of the confidence interval 22 Does this effect hold regardless of sample size 23 Save your file and turn in this sheet with your answers