DEVSQ Function

Returns the sum of squared deviations from the mean.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

Want to practice DEVSQ in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

DEVSQ subtracts the average from each number, squares the result and adds them up. It's the building block of variance: divide by n for VAR.P or by n − 1 for VAR.S.

Regression and ANOVA tables call it the "total sum of squares".

Syntax

=DEVSQ(number1, [number2], …)

Arguments

ArgumentRequired?What it does
number1YesA number, cell or range.
number2, …NoUp to 254 more numbers, cells or ranges.

Examples

Example 1 — Sum of squares, then variance

Divide by the count minus one to get the sample variance.

E2fx=DEVSQ(C2:C8)
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000Sum of squares640733571.43
3BenSouth48500÷ (n − 1)106788928.57
4CyNorth50000
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=DEVSQ(C2:C8) Explain

The second result matches VAR.S(C2:C8).

Common mistakes

  • Text and blanks are skipped. Only numbers are used.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.