StatPakTwo-way ANOVA in Google Sheets
A two-way ANOVA tests two factors at once, for example fertilizer type and watering level, and with repeated measurements also tests whether the factors interact. Excel's ToolPak has two versions and StatPak has both.
| Your data | Tool |
|---|---|
| Several measurements for each combination of the two factors | Anova: Two-Factor With Replication |
| Exactly one measurement for each combination | Anova: Two-Factor Without Replication |
With replication
Eighteen plots: three fertilizers (columns) crossed with two watering levels (row blocks), three plots per combination. Yield in kg per plot.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fertilizer A | Fertilizer B | Fertilizer C | |
| 2 | Low water | 42 | 48 | 44 |
| 3 | 45 | 50 | 43 | |
| 4 | 40 | 47 | 46 | |
| 5 | High water | 50 | 64 | 52 |
| 6 | 53 | 66 | 55 | |
| 7 | 49 | 63 | 51 |
The layout matters. The first row holds the column factor's labels. The first column holds each row-factor label once, on the first row of its block. Every block must have the same number of rows.
- Open Extensions, StatPak, Data Analysis and choose Anova: Two-Factor With Replication.
- Input range:
A1:D7, including the label row and label column. - Rows per sample: 3. Leave Alpha at 0.05 and click Run.
| Anova: Two-Factor With Replication | ||||||
| SUMMARY | Fertilizer A | Fertilizer B | Fertilizer C | Total | ||
| Low water | ||||||
| Count | 3 | 3 | 3 | 9 | ||
| Sum | 127 | 145 | 133 | 405 | ||
| Average | 42.33333 | 48.33333 | 44.33333 | 45 | ||
| Variance | 6.333333 | 2.333333 | 2.333333 | 9.75 | ||
| High water | ||||||
| Count | 3 | 3 | 3 | 9 | ||
| Sum | 152 | 193 | 158 | 503 | ||
| Average | 50.66667 | 64.33333 | 52.66667 | 55.88889 | ||
| Variance | 4.333333 | 2.333333 | 4.333333 | 43.61111 | ||
| Total | ||||||
| Count | 6 | 6 | 6 | |||
| Sum | 279 | 338 | 291 | |||
| Average | 46.5 | 56.33333 | 48.5 | |||
| Variance | 25.1 | 78.66667 | 23.5 | |||
| ANOVA | ||||||
| Source of Variation | SS | df | MS | F | P-value | F crit |
| Sample | 533.5556 | 1 | 533.5556 | 145.5152 | 4.5619E-8 | 4.747225 |
| Columns | 324.1111 | 2 | 162.0556 | 44.19697 | 2.9164E-6 | 3.885294 |
| Interaction | 58.77778 | 2 | 29.38889 | 8.015152 | 0.006156313 | 3.885294 |
| Within | 44 | 12 | 3.666667 | |||
| Total | 960.4444 | 17 |
Reading the result
The ANOVA table has three tests, one per row. Each compares its F with F crit, or equivalently its P-value with 0.05.
- Sample is the row factor (watering). F is 145.5152 with P-value 4.5619E-8, so watering level affects yield.
- Columns is the column factor (fertilizer). F is 44.19697 with P-value 2.9164E-6, so the fertilizers differ.
- Interaction has F 8.015152 and P-value 0.006156313. A significant interaction means the effect of one factor depends on the level of the other. In the summary, high water raises Fertilizer B's average from 48.33333 to 64.33333, a much bigger gain than for A or C.
When the interaction is significant, describe the effects combination by combination (for example with the cell averages in the summary) instead of quoting a single "fertilizer effect".
Without replication
Units sold of three product tiers in four regions, one figure per region and tier.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Basic | Plus | Pro | |
| 2 | North | 120 | 95 | 60 |
| 3 | South | 135 | 102 | 71 |
| 4 | East | 110 | 88 | 52 |
| 5 | West | 142 | 110 | 75 |
Choose Anova: Two-Factor Without Replication, select A1:D5, tick Labels in first row and column and click Run.
| Anova: Two-Factor Without Replication | ||||||
| SUMMARY | Count | Sum | Average | Variance | ||
| North | 3 | 275 | 91.66667 | 908.3333 | ||
| South | 3 | 308 | 102.6667 | 1024.333 | ||
| East | 3 | 250 | 83.33333 | 857.3333 | ||
| West | 3 | 327 | 109 | 1123 | ||
| Basic | 4 | 507 | 126.75 | 208.9167 | ||
| Plus | 4 | 395 | 98.75 | 88.91667 | ||
| Pro | 4 | 258 | 64.5 | 109.6667 | ||
| ANOVA | ||||||
| Source of Variation | SS | df | MS | F | P-value | F crit |
| Rows | 1172.667 | 3 | 390.8889 | 47.06355 | 0.0001458866 | 4.757063 |
| Columns | 7776.167 | 2 | 3888.083 | 468.1304 | 2.5819E-7 | 5.143253 |
| Error | 49.83333 | 6 | 8.305556 | |||
| Total | 8998.667 | 11 |
Rows (regions) has P-value 0.0001458866 and Columns (tiers) has P-value 2.5819E-7, so both region and tier matter. With one value per cell there is no way to separate interaction from random error, so this version has no Interaction row. It assumes the two factors do not interact.
Without an add-on
Google Sheets has no ANOVA function. For the version without replication you can build the table yourself: SS Rows is c * DEVSQ of the row averages, SS Columns is r * DEVSQ of the column averages, SS Total is =DEVSQ(B2:D5), and SS Error is what remains. Each P-value is =F.DIST.RT(F, df, df_error). The version with replication needs cell, row and column averages for every block, which is where the tool saves real time.