Repeated Measures ANOVA With Excel
In this lesson, we show how to conduct analysis of variance for an single-factor, repeated measures experiment with Excel. And we explain how to interpret the results of our analysis.
Note: If you're curious about the computations used by Excel to conduct analysis of variance with a repeated measures design, read the previous lesson: One-Factor Repeated Measures: Example. That lesson shows all of the formulas and computations required to solve the same problem that we will solve in this lesson with Excel.
The Analysis ToolPak
To access the analysis of variance functions in Excel, you need a free Microsoft add-in called the Analysis ToolPak, which may or may not be already installed on your copy of Excel.
To determine whether you have the Analysis ToolPak, click the Data tab in the main Excel menu. If you see Data Analysis in the Analysis section, you're good. You have the ToolPak.
If you don't have the ToolPak, you need to get it. Go to: How to Install the Data Analysis ToolPak in Excel.
Problem Statement
To demonstrate how to conduct analysis of variance for a randomized block experiment with Excel, we'll work through a real-world problem. Here's the problem:
As part of a repeated measures experiment, a researcher tests the effect of three treatments on short-term cognitive performance. Each treatment is administered in pill form. The first treatment (T1) is a placebo; the second treatment (T2) is an herbal relaxant; and the third treatement (T3) is an herbal stimulant. The researcher randomly selects six subjects to participate in the experiment.
Using human subjects as experimental units, the researcher conducts this experiment over a three-day period. Each day, each subject receives a different treatment. After each treatment, subjects complete a memory test. Test scores for each subject following each treatment are shown in the table below:
Table 1. Dependent Variable Scores
| Subject | Test score | ||
|---|---|---|---|
| T1 | T2 | T3 | |
| S1 | 87 | 85 | 87 |
| S2 | 84 | 84 | 85 |
| S3 | 83 | 84 | 84 |
| S4 | 82 | 82 | 83 |
| S5 | 81 | 82 | 83 |
| S6 | 80 | 80 | 82 |
Repeated measures experiments have a potential problem: vulnerability to order effects (e.g., fatigue, learning) that can affect subject performance. To control for order effects, the researcher randomizes the order in which treatment levels are administered.
This experiment is designed to address one main research question: Does the treatment have a significant effect on cognitive performance (as measured by test score)?
Repeated Measures ANOVA With Excel
When you conduct a repeated measures analysis of variance with Excel, the main output is an ANOVA summary table. As we've seen in previous lessons, an ANOVA summary table holds all the information we need to answer the research question posed above.
Here is a step-by-step guide for producing an ANOVA summary table for a repeated measures experiment with Excel:
- Step 1. Enter data from Table 1 in rows and columns of an Excel spreadsheet.
Follow the layout from Table 1, with the column labels in the first row, as shown below:
- Step 2. From Excel's main navigation menu, click Data / Data Analysis
to display the Data Analysis dialog box.
- Step 3. In the Data Analysis dialog box, select "Anova: Two-Factor Without Replication" and click the
OK button to display the Anova: Two-Factor Without Replication dialog box.
- Step 4. In the Anova: Two-Factor Without Replication dialog box, enter the input range.
Click the Labels checkbox to indicate that you included labels for the rows and columns.
And finally, enter a value for Alpha, the significance level.
For this exercise, we'll use a significance level of 0.05, as shown below:
- Step 5. From the Anova: Two-Factor Without Replication dialog box, click the OK button to
display the ANOVA summary table.
Congratulations! You conducted a single-factor, repeated measures analysis of variance with Excel. In the ANOVA table, output for the subject effect appears in the row labelled "Rows". Output for the treatment effect appears in the row labelled "Columns".
Interpretation of Results
Recall that the researcher undertook this study to answer one question: Does the treatment have a significant effect on cognitive performance (as measured by test score)?
The answer to that question can be found in the ANOVA summary table. However, you may need to do a little more work, depending on whether you accept the sphericity assumption in your data.
When Sphericity Is Satisfied
Excel assumes that the sphericity assumption is satisfied. If that assumption is correct, you can interpret results from Excel's ANOVA table in the normal way.
For this study, the P-value (shown in the last column of the ANOVA table) is the probability that an F statistic would be more extreme (bigger) than the F ratio shown in the table, assuming the null hypothesis is true. When the P-value is bigger than the significance level, we accept the null hypothesis; when it is smaller, we reject it.
Here, the P-value for the treatment effect (0.01) is smaller than the significance level (0.05), so we reject the null hypothesis and conclude that pill treatment had a statistically significant effect on test score.
When Sphericity Is Not Satisfied
When the sphericity assumption is violated, the standard F-test in analysis of variance will be positively biased; that is, you will be more likely to make a Type I error (i.e., reject the null hypothesis when it is, in fact, true).
Based on the standard ANOVA table produced by Excel, we rejected the null hypothesis. That analysis would be valid if the sphericity assumption were satisfied. But if the sphericity analysis were violated, that analysis could be misleading - possibly incorrect due to a Type I error.
In the previous lesson, we explained how to correct output from a standard analysis of variance (like the output produced by Excel) to avoid problems when the sphericity assumption is not satisfied. To see what you need to do, read the previous lesson.
