This tutorial will help you set up a Cochran's Q test in Excel using XLSTAT. This statistical test compares dependent samples of binary data.
Not sure this is the statistical test you are looking for? Check out this guide.
Dataset to run a Cochran Q test
An Excel sheet with both the data and the results can be downloaded by clicking on the button below:
Download the data
This tutorial explains how to run a Cochran’s Q test. The data correspond to a survey where several teenagers have been asked to tell if they like packaging of a computer game (answer is Yes in the dataset) or not (answer is No in the dataset). We want to know if there is a significant difference between the packaging options or not, and to decide if one packaging appears to be superior to the other or not before the product goes to production.
As for any test, we need to know what the null and alternative hypotheses are. In our case, the null hypothesis is that there is no difference between the packaging. The alternative hypothesis, that the decision makers would like to be confirmed, is that there is a difference.
Setting up a Cochran's Q test with XLSTAT
After opening XLSTAT, select the XLSTAT / Nonparametric tests / Cochran’s Q test feature.
The Cochran's Q test dialog box appears.
In the General tab, select the data in the Subjects/Treatments table field. Activate the Multiple pairwise comparisons option in order to identify which packaging (one or more) are responsible for rejecting the null hypothesis in case we reject it.
XLSTAT proposes two different pairwise comparisons methods:
a) the Critical difference (Sheskin) method which calculates a critical value CD. When the difference in proportion between two treatments is greater than CD, it is concluded that there is a significant difference between the two treatments. b) the McNemar(Bonferroni) procedure which performs McNemar tests between the different treatment pairs and applies the Bonferroni correction.
The computations begin once you have clicked the OK button.
Interpreting the results of a Cochran's Q test
Then, the results of the Cochran’s Q test and their interpretation are displayed.
We see that the null hypothesis is rejected if we use a significance level of 0.05. As a reminder, choosing a significance level of 0.05 is the same as deciding that we want to be right in 95% of cases (and wrong in 5% of cases) when rejecting H0.
For the decision makers, that means that choosing a packaging at random or using a manager’s preference would not make sense as long as the sample of teenagers has been well chosen. We now want to identify if one or more packaging are responsible for rejecting H0. The following results give us the answer:
We see that the packaging 3 and 1 are different, while the other packaging are not statistically different. However as the packaging that received the more “Yes” is Pack3, it seems to be the best choice.