Statistical
One-Way ANOVA formula: Between-Group vs Within-Group F-statistic
Test whether three or more group means differ significantly by computing SS_Between, SS_Within, Mean Squares, and the One-Way ANOVA F-ratio directly in cells.
Decompose variance into signal (Between) vs noise (Within)
Running multiple pairwise t-tests across 3+ groups inflates false positives. One-Way Analysis of Variance (ANOVA) compares variance BETWEEN group means (MS_Between = SS_Between / (k - 1)) against variance WITHIN groups (MS_Within = SS_Within / (N - k)).
When all groups share equal sample size n, MS_Within is simply the average of the k group variances VAR.S, and SS_Between = n * SUM((GroupMean_i - GrandMean)^2).
| ANOVA Source | Sum of Squares (SS) | df | Mean Square (MS) | F-Ratio |
|---|---|---|---|---|
| Between Groups (k=3) | 3*(25 + 0 + 25) = 150 | k - 1 = 2 | 150 / 2 = 75.00 | 75 / 4 = 18.75 |
| Within Groups (N=9) | 8 + 8 + 8 = 24 | N - k = 6 | 24 / 6 = 4.00 | (Crit F_05 = 5.14) |
| Total | 174 | N - 1 = 8 | — | Reject Equal Means |
Three-group One-Way ANOVA F-statistic formula
Works for unequal or equal group sizes using exact n_i * (m_i - gm)^2 and (n_i - 1) * VAR.S(Group_i).
=LET(gm, AVERAGE(A2:C4), sampleCountA, COUNT(A2:A4), sampleCountB, COUNT(B2:B4), sampleCountC, COUNT(C2:C4), ssb, sampleCountA*(AVERAGE(A2:A4)-gm)^2 + sampleCountB*(AVERAGE(B2:B4)-gm)^2 + sampleCountC*(AVERAGE(C2:C4)-gm)^2, ssw, (sampleCountA-1)*VAR.S(A2:A4) + (sampleCountB-1)*VAR.S(B2:B4) + (sampleCountC-1)*VAR.S(C2:C4), ROUND((ssb / 2) / (ssw / (sampleCountA+sampleCountB+sampleCountC - 3)), 2))Within-Group Mean Square (pooled error variance)
Averages within-group spread weighted by each group's degrees of freedom.
=LET(sampleCountA, COUNT(A2:A4), sampleCountB, COUNT(B2:B4), sampleCountC, COUNT(C2:C4), ((sampleCountA-1)*VAR.S(A2:A4) + (sampleCountB-1)*VAR.S(B2:B4) + (sampleCountC-1)*VAR.S(C2:C4)) / (sampleCountA+sampleCountB+sampleCountC - 3))Inputs and interpretation
The classical one-way ANOVA inference assumes independent observations, approximately normal within-group residuals and equal population variances. This VAR.S implementation requires at least two numeric values per group and positive within-group variance. F does not identify which means differ or establish causation. The rounded 5.14 threshold applies only to df=(2,6); missing observations change the denominator degrees of freedom.
Try the example
Use the original values or change them to test your understanding.