How to Perform McNemar’s Test in Excel

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:

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

H_A (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 X² = (|A-B| – .5)2 / (A+B) = (|12-14| – 1)2 / (12+14) = .03846.

This test statistic follows a X² distribution with one degree of freedom. According to the Chi-Square Distribution table, X²_{(.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:

This results in the same answers as above.