Join Conrad Carlberg for an in-depth discussion in this video Tukey: Test of mean differences in Excel, part of Business Analytics: Multiple Comparisons in R and Excel.
- [Instructor] This worksheet calls out each of the differences between two group means separately. With seven groups, as here, you can make seven times six divided by two comparisons. Each involving a different pair. That's 21. So there are 21 mean comparisons to make. The difference is between group means appear in column h. So for example, the group one mean of 33.02 in cell F2, minus the group two mean of 29.98, in cell G2, results in a mean difference of 3.03 in cell H2. How far away in standard deviation units is that difference of 3.03 from zero? We can determine that by dividing the raw difference by the standard error within, which we saw in this case is 3.22. It's in cell B11, on a current worksheet. The result of the division 3.03 divided by 3.22 is 0.94, shown in cell I2. So the two group means are 0.94 of a standard error apart. Finally we take the absolute value of that result in cell J2. That tells us that the raw difference between the means of cells channels f and c is 94% of a standard error in the q distribution. Does that different between the two means depart from zero far enough to make you believe that it's a dependable difference? The critical q value shown in cell B13 answers that question. A difference of at least 3.86 standard deviation units is necessary for the difference between any two of these group means to be regarded as significant at the 0.10 level of alpha. If you scan the values in column j, you'll see that only one in row seven is large enough at 4.07 to be declared significant. It's the only one of the 21 comparisons with an absolute value larger than the critical value of 3.86. So according to Tukey's multiple comparison procedure, there's a significant difference only between the mean profit margin for sales channels c and the margin for sales channel e. If you'd rather think in terms of margin dollars instead of standard error units, you can multiply the number of standard errors in cell B13, by the size of the standard error in cell B11. This value 12.42 is the critical value for any of the 21 comparisons of group means in dollar units. As you'll see it's also the size of the half width of the 90% confidence interval. Let's compare the results we got on Excel with results that we get from the statistical freeware package R.