Home Â» How to Perform a Two Proportion Z-Test in Excel

# How to Perform a Two Proportion Z-Test in Excel

AÂ two proportion z-testÂ is used toÂ test for a difference between two population proportions.

For example, suppose a superintendent of a school district claims that the percentage of students who prefer chocolate milk over regular milk in school cafeterias is the same for school 1 and school 2.

To test this claim, an independent researcher obtains aÂ simple random sampleÂ of 100 students from each school and surveys them about their preferences. He finds that 70% of students prefer chocolate milk in school 1 and 68% of students prefer chocolate milk in school 2.

We can use a two proportion z-test to test whether or not the percentage of students who prefer chocolate milk over regular milk is the same for both schools.

## Steps to Perform a Two Sample Z-Test

We can use the following steps to perform the two proportion z-test:

Step 1. State the hypotheses.Â

The null hypothesis (H0):Â P1 = P2

The alternative hypothesis: (Ha):Â P1 â‰  P2

Step 2. Find the test statistic and the corresponding p-value.

First, find the pooled sample proportion p:

p = (p1Â * n1Â + p2Â * n2) / (n1Â + n2)

p = (.70*100 + .68*100) / (100 + 100) = .69

Then use p in the following formula to find the test statistic z:

z = (p1-p2) /Â âˆšp * (1-p) * [ (1/n1) + (1/n2)]

z = (.70-.68) /Â âˆš.69 * (1-.69) * [ (1/100) + (1/100)]Â = .02 / .0654 =Â .306

Use theÂ Z Score to P Value CalculatorÂ with a z score of .306 and a two-tailed test to find that the p-value =Â 0.759.

Step 3. Reject or fail to reject the null hypothesis.

First, we need to choose a significance level to use for the test. Common choices are 0.01, 0.05, and 0.10. For this example, letâ€™s use 0.05. Since the p-value is not less than our significance level of .05, we fail to reject the null hypothesis.

Thus,Â we do not have sufficient evidence to say that the percentage of students who prefer chocolate milk is different for school 1 and school 2.

## How to Perform a Two Sample Z-Test in Excel

The following examples illustrate how to perform a two sample z-test in Excel.

### Two Sample Z Test (Two-tailed)

A superintendent of a school district claims that the percentage of students who prefer chocolate milk over regular milk in school cafeterias is the same for school 1 and school 2.

To test this claim, an independent researcher obtains a simple random sample of 100 students from each school and surveys them about their preferences. He finds that 70% of students prefer chocolate milk in school 1 and 68% of students prefer chocolate milk in school 2.

Based on these results, can we reject the superintendentâ€™s claim that the percentage of students who prefer chocolate milk is the same for school 1 and school 2? Use a .05 level of significance.Â

The following screenshot shows how to perform a two-tailed two sample z test in Excel, along with the formulas used:

You need to fill in the values for cellsÂ B1:B4. Then, the values for cellsÂ B6:B8Â are automatically calculated using the formulas shown in cellsÂ C6:C8.

Note that the formulas shown do the following:

• Formula in cellÂ C6: This calculates the pooled sample proportion using the formulaÂ p =Â (p1Â * n1Â + p2Â * n2) / (n1Â + n2)
• Formula in cellÂ C7: This calculates the test statistic zÂ using the formulaÂ z = (p1-p2) /Â âˆšp * (1-p) * [ (1/n1) + (1/n2)] whereÂ pÂ is the pooled sample proportion.
• Formula in cellÂ C8: This calculates the p-value associated with the test statistic calculated in cell B7Â using the Excel function NORM.S.DIST, which returns the cumulative probability for the normal distribution with mean = 0 and standard deviation = 1. We multiply this value by two since this is a two-tailed test.

Since the p-valueÂ (0.759) is not less than our chosen significance level ofÂ 0.05,Â we fail to reject the null hypothesis.Â Thus,Â we do not have sufficient evidence to say that the percentage of students who prefer chocolate milk is different for school 1 and school 2.

### Two Sample Z Test (One-tailed)

A superintendent of a school district claims that the percentage of students who prefer chocolate milk over regular milk in school 1 is less than or equal to the percentage in school 2.

To test this claim, an independent researcher obtains a simple random sample of 100 students from each school and surveys them about their preferences. He finds that 70% of students prefer chocolate milk in school 1 and 68% of students prefer chocolate milk in school 2.

Based on these results, can we reject the superintendentâ€™s claim that the percentage of students who prefer chocolate milk in school 1 is less than or equal to the percentage in school 2? Use a .05 level of significance.Â

The following screenshot shows how to perform a one-tailed two sample z test in Excel, along with the formulas used:

You need to fill in the values for cellsÂ B1:B4. Then, the values for cellsÂ B6:B8Â are automatically calculated using the formulas shown in cellsÂ C6:C8.

Note that the formulas shown do the following:

• Formula in cellÂ C6: This calculates the pooled sample proportion using the formulaÂ p =Â (p1Â * n1Â + p2Â * n2) / (n1Â + n2)
• Formula in cellÂ C7: This calculates the test statistic zÂ using the formulaÂ z = (p1-p2) /Â âˆšp * (1-p) * [ (1/n1) + (1/n2)] whereÂ pÂ is the pooled sample proportion.
• Formula in cellÂ C8: This calculates the p-value associated with the test statistic calculated in cell B7Â using the Excel function NORM.S.DIST, which returns the cumulative probability for the normal distribution with mean = 0 and standard deviation = 1.

Since the p-valueÂ (0.379) is not less than our chosen significance level ofÂ 0.05,Â we fail to reject the null hypothesis.Â Thus,Â we do not have sufficient evidence to say that the percentage of students who prefer chocolate milk in school 2 is greater than that of school 1.