Question

assignament for excel...is there any way to upload the table this creates??

In this activity, we use data from a random sample of women who participated in a study in 1986 at the Baystate Medical Center in Springfield, MA. (Source: Hosmer and Lemeshow (2000), Applied Logistic Regression: Second Edition.)

For the 30 women in the study with a history of premature labor, a proportion of 18/30 = 0.60 (60%) had babies with low birth weight. For the remaining 159 women, a proportion of 41/159 = 0.26 (26%) had babies with low birth weight.

We now investigate the following research question: do the data provide evidence that the proportion of babies born with low birth weight is higher for women with a history of premature labor? This question is answered with a hypothesis test. To conduct the test we use a 1% level of significance.

1. Enter labels x1 and x2 in cells A1 and B1.

2. Below these labels, in cells A2 and B2, enter the numbers of successes for the two samples.

3. Enter labels n1 and n2 in cells A3 and B3.

4. Below these labels, in cells A4 and B4, enter the sizes of the two samples.

5. Enter the labels p-hat1 and p-hat2 in cells A5 and B5.

6. In cell A6, enter the formula to compute first sample proportions.

=A2/A4

7. In cell B6, enter the formula to compute first sample proportions.

=B2/B4

8. In cell C1, enter the label p-bar.

9. In cell C2, enter the formula for the pooled proportion.

=(A2+B2)/(A4+B4)

10. In cell C3, enter the label for the test statistic, Z.

11. In cell C4, enter the formula for the test statistic.

=(A6-B6-0)/SQRT(C2*(1-C2)/A4+C2*(1-C2)/B4)

12. In cell C5, enter the label, P-value.

13. In cell C6, enter the formula for the P-value.

=1-NORMSDIST(C4)

Answer 1

Z Test for Differences in Two Proportions
Data
Hypothesized Difference	0
Level of Significance	0.05
Group 1
Number of Successes	18
Sample Size	30
Group 2
Number of Successes	41
Sample Size	159
Intermediate Calculations
Group 1 Proportion	0.6000
Group 2 Proportion	0.2579
Difference in Two Proportions	0.3421
Average Proportion	0.3122
Z Test Statistic	3.7093
Two-Tail Test
Lower Critical Value	-1.9600
Upper Critical Value	1.9600
p-Value	0.0001
Reject the null hypothesis

Excel formulas

Z Test for Differences in Two Proportions
Data
Hypothesized Difference	0
Level of Significance	0.05
Group 1
Number of Successes	18
Sample Size	30
Group 2
Number of Successes	41
Sample Size	159
Intermediate Calculations
Group 1 Proportion	=B7/B8
Group 2 Proportion	=B10/B11
Difference in Two Proportions	=B14 - B15
Average Proportion	=(B7 + B10)/(B8 + B11)
Z Test Statistic	=(B16 - B4)/SQRT(B17 * (1 - B17) * (1/B8 + 1/B11))
Two-Tail Test
Lower Critical Value	=NORM.S.INV(B5/2)
Upper Critical Value	=NORM.S.INV(1 - B5/2)
p-Value	= (1 - NORM.S.DIST(ABS(B18), TRUE))
=IF(B23 < B5, "Reject the null hypothesis", "Do not reject the null hypothesis")