Stat Trek

Teach yourself statistics

Stat Trek

Teach yourself statistics

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.

Excel Main menu
Excel Main menu

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:
    Rows and columns in Excel spreadsheet
  • Step 2. From Excel's main navigation menu, click Data / Data Analysis to display the Data Analysis dialog box.
    Excel Main menu
    Excel Main menu
  • 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.
    Data Analysis dialog box from Excel
  • 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:
    Anova: Two-Factor Without Replication dialog box from Excel
  • Step 5. From the Anova: Two-Factor Without Replication dialog box, click the OK button to display the ANOVA summary table.
    ANOVA summary table generated by Excel

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:

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).