Randomized Block Experiment With Excel
In this lesson, we show how to conduct analysis of variance for an independent groups, randomized block 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 randomized block design, read the previous lesson: Randomized Block Experiment: 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 randomized block experiment, a researcher tests the effect of three teaching methods on student performance. The researcher selects subjects randomly from a student population. The researcher assigns subjects to six blocks of three, such that students within the same block have the same (or similar) IQ. Within each block, each student is randomly assigned to a different teaching method.
At the end of the term, the researcher collects one test score (the dependent variable) from each subject, as shown in the table below:
Table 1. Dependent Variable Scores
| IQ | Teaching Method | ||
|---|---|---|---|
| A | B | C | |
| 91-95 | 84 | 85 | 85 |
| 96-100 | 86 | 86 | 88 |
| 101-105 | 86 | 87 | 88 |
| 106-110 | 89 | 88 | 89 |
| 111-115 | 88 | 89 | 89 |
| 116-120 | 91 | 90 | 91 |
In conducting this experiment, the researcher has two research questions:
- Does teaching method have a significant effect on student performance (as measured by test score)?
- How strong is the effect of teaching method on the student performance?
Randomized Block ANOVA With Excel
When you conduct a randomized block 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 questions posed above.
Here is a step-by-step guide for producing an ANOVA summary table for a randomized block 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 independent variable (teaching method) in columns and the
blocking variable (IQ) in rows. Include labels for rows and columns, 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.
Interpretation of Results
Recall that the researchers undertook this study to answer two questions:
- Does teaching method have a significant effect on student performance (as measured by test score)?
- How strong is the effect of teaching method on the student performance?
Answers to both questions can be found in the ANOVA summary table.
Statistical Significance
For this study, analysis of variance tested two hypotheses:
- The null hypothesis that the independent variable (teaching method) had no effect on the dependent variable (test score).
- The null hypothesis that the blocking variable (IQ) had no effect on the dependent variable (test score).
Each 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.
- The P-value for the blocking variable (0.00) is smaller than the significance level (0.05), so we reject the null hypothesis and conclude that IQ had a statistically significant effect on test score.
- The P-value for the treatment variable (0.04) is also smaller than the significance level (0.05), so we reject the null hypothesis and conclude that teaching method had a statistically significant effect on test score.
Magnitude of Effect
To assess the strength of the effect for the treatment variable or for the blocking variable, an experimenter might compute eta squared (η2). The computation is easy, using sum of squares entries from the ANOVA table, as shown below:
η2treatment = SSTR / SST = 3.44 / 71.61 = 0.05
η2blocks = SSB / SST = 64.28 / 71.61 = 0.90
where SSTR is the sum or squares for treatments, SSB is the sum of squares for blocks, and SST is the total sum of squares.
For this experiment, an eta squared of 0.05 means that 5% of the variance in the dependent variable (test score) can be explained by the effect of the independent variable (teaching method). And an eta squared of 0.90 means that 90% of the variance in the dependent variable (test score) can be explained by the effect of the blocking variable (IQ).
