Table of Contents
A two proportion z-test in Excel is a statistical tool used to compare the difference between two proportions of categorical data. It tests the null hypothesis that the proportions of the two groups are equal. This can be done by running a Z test in Excel using the two-proportion z-test function and entering the group sizes, sample proportions, and hypothesized proportion. This will return a P-value which can be used to assess the null hypothesis.
A 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 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 with a z score of .306 and a two-tailed test to find that the p-value = 0.759.
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.