How to Perform a Two Proportion Z-Test in Excel


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:

Two-tailed two sample proportion z test in Excel

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 using the formula z = (p1-p2) / √p * (1-p) * [ (1/n1) + (1/n2)] where 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:

Two sample proportion z test in Excel

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 using the formula z = (p1-p2) / √p * (1-p) * [ (1/n1) + (1/n2)] where 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.

Featured Posts

5 Replies to “How to Perform a Two Proportion Z-Test in Excel”

  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.

  2. 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!

  3. 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!

  4. 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

Leave a Reply

Your email address will not be published. Required fields are marked *