LAB 9 INSTRUCTIONS FOR EXCEL 2007

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 Data>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 OutputRange; 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 and 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.

  1. 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.
  2. 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
  3. 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)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”

2)On line 111, in col B enter 90%, in col C enter 1.645, in col D enter 20, in col E enter 10000.

3)In F111 enter =E111-C111*1000/SQRT(D111) to calculate Lower Confidence Limit.

4)In G111 enter =E111 +C111*1000/SQRT(D111) to calculate Upper Confidence Limit.

5)In H111 enter = G111-F111 to calculate Width of Confidence Interval.

6)On line 112 copy line 111 down and then change the sample size in col D to 80,

7)On line 113, copy line 112 down and then change the sample size in col D to 320.

8)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.

9)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.

10)Format the widths in H111:H119 to 1 decimal place.

11)In the table below enter the confidence widths from col H.

WIDTH OF / CONFIDENCE / INTERVAL
Sample Size
20 / 80 / 320
Confidence Level
90% / H111= / H112= / H113=
95% / H114= / H115= / H116
99% / H117= / H118= / H119=
  1. 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 ______.

  1. When the sample size is quadrupled, what is the ratio of the new width to the old width? ______
  2. Does this ratio hold regardless of confidence level? ______
  1. Holding sample size constant, as confidence level is changed from 90% to 95% to 99%, the width of the confidence interval ______.
  1. Does this effect hold regardless of sample size? ______.
  1. Save your file and turn in this sheet with your answers.