We show how to perform a Randomized Complete Block Design (RCBD) in Excel using regression. The approach is similar to that shown in ANOVA using Regression and uses dummy variables. The technique is illustrated for Example 1 of Randomized Complete Block Design.
Figure 1 – Dummy coding of RCBD for regression
Figure 1 shows the dummy variable coding for Example 1. There are 5 dummy variables (T10, T15, T20, T25, T30) corresponding to the treatment groups and 3 dummy variables (F2, F3, F4) corresponding to the fields (the blocking factor). We now use the Real Statistics Multiple Regression data analysis tool with X Range I2:Q26 and Y Range R2:Q26 to get the output shown in Figure 2 (only the portion of the output that we need is displayed).
Figure 2 – Regression (full model)
Next, we use the Real Statistics Multiple Regression data analysis tool with X Range I2:M26 and Y Range R2:Q26 to get the output shown in Figure 3.
Figure 3 – Regression (subset model)
Using Figure 2 and 3, we can obtain the output shown in Figure 4.
Figure 4 – RCBD using Regression
The Error and Total SS and df values come directly from Figure 2 (e.g. cell AU17 and AU18 contain the formulas =U14 and =U15. The Groups value comes from Figure 3 (e.g. cell AU16 contains the formula =AD13). Finally, the Blocks values are derived by subtracting the Groups values from the Regression values of Figure 2 (e.g. cell AU15 contains the formula =U13-AU16).
Note that Figure 4 is identical to Figure 3 of Randomized Complete Block Design.