Statistical & Engineering
Sum of Squared Deviations (DEVSQ) & Pooled Within-Group Variance Formula
Compute the exact Sum of Squared Deviations from the sample mean SS = (COUNT(range) - 1) * VAR.S(range) and combine two independent group SS terms into a pooled ANOVA / t-test variance.
Extract exact Sum of Squares (SS) for ANOVA, regression, and pooled t-tests!
Because sample variance is defined as s^2 = Sum((x_i - mean)^2) / (n - 1), multiplying (COUNT(range) - 1) * VAR.S(range) (or equivalently COUNT(range) * VAR.P(range)) recovers the exact Sum of Squared Deviations SS (DEVSQ) for any numeric range without needing a row-by-row (x_i - mean)^2 helper column!
Summing SS_A + SS_B across two experimental batches and dividing by total degrees of freedom (n_A + n_B - 2) gives the exact Equal-Variance Pooled Variance s_p^2 = (SS_A + SS_B) / (n_A + n_B - 2) used in two-sample Student t-tests and one-way ANOVA!
| Metric | Batch A [12, 15, 18, 21, 24] | Batch B [10, 14, 18, 22] | Pooled Combined |
|---|---|---|---|
| Count (n) / df (n - 1) | 5 / df = 4 | 4 / df = 3 | df_total = 7 |
| Sample Variance (VAR.S) | 22.50 | 26.6667 | s_p^2 = 24.2857 |
| Sum of Squares (DEVSQ) | 90.00 | 80.00 | SS_within = 170.00 |
Sum of Squared Deviations (SS / DEVSQ) for Batch A
Recovers Sum((x_i - x_bar)^2) across B2:B6 via COUNT(B2:B6) * VAR.P(B2:B6).
=IF(COUNT(B2:B6) < 1, "Empty Sample", ROUND(COUNT(B2:B6) * VAR.P(B2:B6), 4))Pooled Within-Group Variance Across Batch A (B2:B6) & Batch B (C2:C5)
Combines SS_A + SS_B divided by (n_A + n_B - 2) degrees of freedom.
=IF(COUNT(B2:B6) + COUNT(C2:C5) <= 2, "Insufficient df", ROUND((COUNT(B2:B6) * VAR.P(B2:B6) + COUNT(C2:C5) * VAR.P(C2:C5)) / (COUNT(B2:B6) + COUNT(C2:C5) - 2), 4))Try the example
Use the original values or change them to test your understanding.