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.
Thank you for spreading this particular wonderful subject matter on your web site. I discovered it on the internet. I am going to check back again if you publish extra aricles.
Hi Zach! Thanks for the helpful commentary.
I believe I have one correction for your excel formulas. Specifically, for your cell C7, you use the formula:
z = (p1-p2) / √p * (1-p) * [ (1/n1) + (1/n2)]
where it should be:
z = ABS(p1-p2) / √p * (1-p) * [ (1/n1) + (1/n2)]
So that the result depends only on the magnitude of the difference between proportions, not the sign of the difference.
Hope this is useful for future readers!
Hi Zach,
Thanks for creating these websites, they are vary useful. I have a question though: when comparing two proportions, e.g. the two schools being equal or not at 70/100 and 68/100. The excel example gives a nice number, but when I start varying the inputs, leave school 1 at 70/100 and running school 2 from 50/100 to 85 /100, I would expect p to go from 1 over 70/100, which does not make sense. What am I missing?
Thanks for your insight!
When computing the p-value, should we first absolute the Z-statistic?
Hi Zach,
I am given a set of 5 different age groups, each with their own “mean” and “standard deviation” figures. After a student completes a questionnaire and I receive his “raw score,” knowing his date of birth and therefore, plugging in the correct mean and standard deviation numerals… how would I arrive at his percentage score given his
raw score
mean score
and standard deviation?
Thanks for your help!!
Becky
Hi Zach,
How do you calculate when the sample sizes are different?
I tried using the Excel instructions above and the test statistic returned #NUM!. I traced the error, it indicates that the SQRT a negative number is the issue. For example SQRT(-351.1) led to this error. How do I resolve this?
My sample proportion 1 is 9.2320, sample size 1 is 120, sample proportion 2 is 2.2805 and sample size 2 is 139. My pooled proportion is 150.87
Thank you for any guidance your can provide.
Teresa
Hi Zach,
How do you calculate when the sample sizes are different?
I tried using the Excel instructions above and the test statistic returned #NUM!. I traced the error, it indicates that the SQRT a negative number is the issue. For example SQRT(-351.1) led to this error. How do I resolve this?
My sample proportion 1 is 9.2320, sample size 1 is 120, sample proportion 2 is 2.2805 and sample size 2 is 139. My pooled proportion is 150.87
Thank you for any guidance your can provide.
Teresa
Hi Teresa,
The error you’re encountering (#NUM!) in Excel indicates that the formula involves attempting to calculate the square root of a negative number. This typically happens when there is a mistake in the formula for calculating the test statistic. Here’s how to resolve it:
### Steps to Resolve:
1. **Revisit the Formula for Two-Sample Z-Test for Proportions**
The test statistic \( Z \) is calculated as:
\[
Z = \frac{\hat{p}_1 – \hat{p}_2}{\sqrt{\hat{p}(1 – \hat{p}) \left(\frac{1}{n_1} + \frac{1}{n_2}\right)}}
\]
– \( \hat{p}_1 \) and \( \hat{p}_2 \): Sample proportions
– \( n_1 \) and \( n_2 \): Sample sizes
– \( \hat{p} \): Pooled proportion, calculated as:
\[
\hat{p} = \frac{x_1 + x_2}{n_1 + n_2}
\]
Where \( x_1 = \hat{p}_1 \cdot n_1 \) and \( x_2 = \hat{p}_2 \cdot n_2 \).
2. **Check for Logical Errors**
Ensure the pooled proportion \( \hat{p} \) and the variance \( \hat{p}(1 – \hat{p}) \) are valid. Variances should never be negative; if \( \hat{p} \) is greater than 1 or less than 0, you might have calculation errors or incorrectly labeled data.
3. **Resolve Negative Variance Issue**
Based on your data:
– \( \hat{p}_1 = 9.2320 \) and \( n_1 = 120 \) suggest \( x_1 = \hat{p}_1 \cdot n_1 = 1107.84 \).
– \( \hat{p}_2 = 2.2805 \) and \( n_2 = 139 \) suggest \( x_2 = \hat{p}_2 \cdot n_2 = 317.99 \).
– Your pooled proportion \( \hat{p} = \frac{x_1 + x_2}{n_1 + n_2} = \frac{1107.84 + 317.99}{120 + 139} \).
However, your stated pooled proportion of \( 150.87 \) seems unusually high, suggesting either a calculation or data entry error. Double-check this step.
4. **Adjust Excel Calculations**
Use the following structure in Excel:
– Calculate \( x_1 = \hat{p}_1 \times n_1 \) and \( x_2 = \hat{p}_2 \times n_2 \).
– Calculate \( \hat{p} = \frac{x_1 + x_2}{n_1 + n_2} \).
– Calculate the denominator:
\[
\sqrt{\hat{p} \cdot (1 – \hat{p}) \cdot \left(\frac{1}{n_1} + \frac{1}{n_2}\right)}
\]
Ensure the values inside the square root are non-negative.
5. **Check Data Integrity**
– Proportions (\( \hat{p}_1 \) and \( \hat{p}_2 \)) should lie between 0 and 1. If they are larger, verify your inputs to confirm if they are proportions or counts.
### Common Issue:
The error is likely due to treating raw counts as proportions or an incorrect pooled proportion calculation. If you adjust the data and still encounter problems, feel free to share more details for further troubleshooting.
Best regards,
Zach