McNemar’s Test in Excel

McNemar's test in Excel tutorial

McNemar’s test is used to test whether or not counts are consistent across two groups. It is often used to test if the counts between a treatment group and control group are equal.

Note: This test is appropriate to use when the same subjects show up in both the control and treatment group.

This tutorial shows how to conduct McNemar’s Test in Excel.

Mcnemar’s Test in Excel

Suppose researchers survey 100 people about a certain law and find that 30 support the law while 70 oppose the law. Researchers then show this group of 100 people a video about the financial benefits of the law and repeat the survey again. This time, 12 people go from supporting to opposing the law while 14 go from opposing to supporting the law.

Conduct a McNemar’s Test to determine if this video had a significant impact on people’s opinion.

First, state the null and alternative hypothesis for the test:

H0 (null hypothesis) = Video has no impact on people’s opinion

HA (alternative hypothesis) = Video has a significant impact on people’s opinion

Next, identify the following:

A = # People who changed from supporting to opposing the law: 12

B = # People who changed from opposing to supporting the law: 14

The test statistic X2 = (|A-B| – .5)2 / (A+B) = (|12-14| – 1)2 / (12+14) = .03846.

This test statistic follows a X2 distribution with one degree of freedom. According to the Chi-Square Distribution table, X2(.05, 1) = 3.841.

Since our test statistic is less than 3.841, we fail to reject the null hypothesis. We do not have sufficient evidence to say that the video had a significant impact on people’s opinion.

To perform this McNemar Test in Excel, simply use the following formulas:

McNemar's Test in Excel

This results in the same answers as above.

Helpful Tools

McNemar’s Test Calculator

Leave a Reply

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