Excel like a finance pro.
← The libraryPractice · 2 questions →
Statistical function
DEVSQ
Returns the sum of squared deviations from the mean.
Rarely usedDifficulty 1300 · ProficientUsage rank #302 of 520
When to use it
The sum of squared deviations from the mean. The raw ingredient of variance, ANOVA, and regression sums of squares.
The shape of it
- Syntax
=DEVSQ(number1, [number2], ...)
Worked examples
Eight values
=DEVSQ(2,4,4,4,5,5,7,9) → 32
The numerator of the variance for the shared eight values.
Back to variance
=DEVSQ(B2:B13)/11 → 2,016,400
Divided by n - 1 it is VAR.S.
Regression ingredient
=DEVSQ(A2:A13) → 143
The Sxx term in a regression.
Worth knowing
- Slope = SUMPRODUCT of deviations / DEVSQ(x).
- Total sum of squares in ANOVA is DEVSQ of all observations.
- Same as SUMSQ(range-AVERAGE(range)) as an array.
Where it goes wrong
- Units are squared.
- Blanks are ignored, changing n.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.