LAB13 - Purdue University



LAB12 Instructions and Questions for Excel 2003

CONFIDENCE INTERVALS FOR POPULATION PROPORTION

NAME________________________LAB TIME_______LAB BLDG______________

1. In this lab we will examine the procedure for estimation of the proportion of a population which would favor a certain issue.

2. To estimate the population proportion a random sample of people is selected and each person is asked whether he/she supports the issue in question.

3. The sample proportion, P-HAT, = number of “yes answers” / total number of answers.

4. The formula for the Confidence Interval for the population proportion is:

P-HAT + / - Z* SQRT ( (P-HAT) (1 – P-HAT) / N )

5. PART A: SETTING UP YOUR SPREADSHEET

6. Use rows 2 and 3 for column headings as follows:

Column A: Leave blank

Column B: Sample size

Column C: Confidence Level

Column D: Z*

Column E: P-HAT

Column F: Margin Of Error

Column G: Confidence Interval

Column H: Leave Blank

Column I: Interval Width

7. Lets pretend that a sample of 1000 people is taken, and 500 people answer “yes”, and 500 people answer “no” on the issue in question.

8. Calculate a 95% Confidence Interval for the population proportion as follows:

In B5: enter 1000

In C5: enter .95 and format cell to display a percentage with 0 decimal places.

In D5: enter value of Z* for 95% confidence: 1.960

In E5: enter the value of P-HAT, 0.5

In F5: enter the formula: = D5*SQRT (E5* ( 1- E5) / B5)

In G5: enter the formula for the low limit: E5 – F5

In H5: enter the formula for the high limit: E5 + F5

In I5: enter the formula for the interval’s width: H5 – G5

9. For n = 1000, P-HAT = 0.5, the 95% Confidence Interval is __________________

10. The margin of error in the above calculation is: _________________.

11. PART B: EFFECT OF THE CONFIDENCE LEVEL ON THE MARGIN OF ERROR AND THE WIDTH OF THE CONFIDENCE INTERVAL.

12. Enter 1000 in B8 through B12 to hold sample size constant at 1000.

13. Enter 0.5 in E8 through E12 to hold p-hat constant at 0.5

14. Enter the confidence levels given below in C8 through C12. Enter the corresponding values of Z* in D8 through D12.

80% 1.282

90% 1.645

95% 1.960

99% 2.576

99.9% 3.291

15. Copy the formulas in F5 : I5 down to F8 : I8. The copy them down through line 12.

16. Use X/Y Scatterplot to plot the margin of error on Y axis vs confidence level on X axis, and place the graph in the spreadsheet using the space J8:P23.

17. A 99% confidence interval for the population proportion would be wider ___ or narrower ____ than a 90% confidence interval.

18. PART C: EFFECT OF SAMPLE SIZE ON THE MARGIN OF ERROR AND THE WIDTH OF THE CONFIDENCE INTERVAL.

19. In B25 through B29 enter the sample sizes 500, 1000, 2000, 4000 and 8000.

20. We will use 95% confidence with z = 1.96 and we will use P-HAT = 0.5. Enter these values in C25, D25 and E25 and then copy them down through line 29.

21. Copy the formulas from F12:I12 to F25:I25. Then copy them down through line 29.

22. Use X/Y scatterplot to plot the margin of error (y axis) vs the sample size (x axis), and place the graph in the spreadsheet using the space J25:P39.

23. As the sample size increases, the margin of error and the width of the confidence interval:

1) Increases _________

2) Decreases _________

24. To cut the margin of error in half, the sample size must be _______________ by a factor of _____________.

25. PART D: EFFECT OF THE PROPORTION ON THE MARGIN OF ERROR AND THE WIDTH OF THE CONFIDENCE INTERVAL.

26. We will hold the sample size constant at 1000, and the confidence level constant at 95%. Enter 1000 in B41, 95% in C41, 1.96 in D41, and copy these values down through line 53.

27. In Column E on lines 41 through 53 enter the following values of P-HAT:

.005

.05

.1

.2

.3

.4

.5

.6

.7

.8

.9

.95

.995

28. Copy the formulas in F29:I29 and paste them in F41:I41. Then copy them down through line 53.

29. Use X/Y Scatterplot to plot the margin of error (y axis) vs P-HAT (x axis) and put the graph in the spreadsheet using the space J41:P57.

30. What value of the proportion produces the maximum margin of error?________

31. PART E: EFFECT OF THE PROPORTION ON THE SAMPLE SIZE REQUIRED TO OBTAIN A DESIRED MARGIN OF ERROR.

32. In C59 enter 95% and in D59 enter 1.96. Enter .03 in F59. Then copy these values down through line 71.

33. Enter the following values for the proportion in Column E, lines 59-71:

.005

.05

.10

.20

.30

.40

.50

.60

.70

.80

.90

.95

.995

34. In B59 enter the formula to calculate the sample size :

= E59*(1-E59)* (D59 / F59 ) ^2

35. Copy the formula in B59 down through B71.

36. Use X/Y scatterplot to plot the sample size ( y axis ) vs the proportion ( x axis), and put the graph in the spreadsheet using the space J59:P71.

37. Print the spreadsheet from A1 to P71 on one page and turn in with your answers. The spreadsheet should have four graphs on it.

38. What value of the proportion requires the largest sample size in order to maintain

a margin of error of .03? ______________________

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download