Table of Contents
McNemar’s Test is a statistical method used to analyze the significance of changes in a binary variable between two related groups. This test is commonly used in research studies to compare the proportions of two paired groups before and after an intervention. To perform McNemar’s Test in Excel, first, organize your data in a contingency table with the two groups as rows and the binary variable as columns. Then, use the “Data Analysis” tool in Excel to run the McNemar’s Test, selecting the appropriate options for the test and inputting the contingency table data. The results will provide the p-value, which can be used to determine the significance of the changes in the binary variable between the two groups.
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:
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:
This results in the same answers as above.