StatPakHow to do a one-way ANOVA in Google Sheets
A one-way ANOVA tests whether three or more group means are equal. Example: test scores from three teaching methods, ten students each.
| A | B | C | |
|---|---|---|---|
| 1 | Method A | Method B | Method C |
| 2 | 78 | 71 | 64 |
| 3 | 85 | 79 | 72 |
| 4 | 69 | 65 | 58 |
| 5 | 91 | 84 | 70 |
| 6 | 74 | 70 | 66 |
| 7 | 88 | 76 | 61 |
| 8 | 82 | 73 | 69 |
| 9 | 77 | 69 | 63 |
| 10 | 80 | 75 | 67 |
| 11 | 86 | 78 | 60 |
Steps
- Open Extensions, StatPak, Data Analysis and choose Anova: Single Factor.
- Input range:
A1:C11with Grouped by: Columns. - Tick Labels in first row or column. Leave Alpha at 0.05.
- Click Run.
| Anova: Single Factor | ||||||
| SUMMARY | ||||||
| Groups | Count | Sum | Average | Variance | ||
| Method A | 10 | 810 | 81 | 45.55556 | ||
| Method B | 10 | 740 | 74 | 30.88889 | ||
| Method C | 10 | 650 | 65 | 21.11111 | ||
| ANOVA | ||||||
| Source of Variation | SS | df | MS | F | P-value | F crit |
| Between Groups | 1286.667 | 2 | 643.3333 | 19.7836 | 5.1215E-6 | 3.354131 |
| Within Groups | 878 | 27 | 32.51852 | |||
| Total | 2164.667 | 29 |
Reading the result
Look at the Between Groups row. F is 19.7836 and the P-value is 5.1215E-6. Because the p-value is below 0.05 (equivalently, F is larger than F crit, 3.354131), at least one method has a different mean score.
ANOVA does not say which groups differ. Follow up with t-tests between pairs, adjusting alpha for the number of comparisons (for three pairs, 0.05 / 3 is about 0.017).
Without an add-on
Google Sheets has no ANOVA function. You can rebuild the table: SS between is the sum over groups of COUNT(g) * (AVERAGE(g) - grand mean)^2, SS within is the sum of DEVSQ(g), then F is (SSB / (k - 1)) / (SSW / (N - k)) and the p-value is =F.DIST.RT(F, k - 1, N - k).
Two-factor ANOVA
For two factors (for example method and school), use Anova: Two-Factor Without Replication when each combination has one value, or With Replication when each combination has several rows. The input needs a label row and a label column.