The Excel DEVSQ function returns the sum of the squared deviations of each value from the data set's mean — the building block behind variance, standard deviation, and least-squares regression.
Syntax
| Argument | Description | |
|---|---|---|
number1 | Required | The first value, reference, or array for which you want the sum of squared deviations. |
number2, ... | Optional | Up to 254 additional numbers or ranges. You can use a single array or up to 255 arguments. |
How to use it
DEVSQ first finds the mean of all the values, then for each value subtracts the mean, squares the result, and adds them all up. For {4,5,8,7,11,4,3} the mean is 6, and the squared deviations (4, 1, 4, 1, 25, 4, 9) sum to 48.
This quantity, the sum of squares, is the numerator of the variance formulas: dividing DEVSQ by n−1 gives VAR.S, and dividing by n gives VAR.P. It also appears throughout regression as the total or residual sum of squares.
Text, logical values, and empty cells inside a referenced range are ignored, so DEVSQ is safe to point at a column that mixes numbers with headers or blanks.
Tip: DEVSQ is more numerically stable than computing SUMPRODUCT((data-AVERAGE(data))^2) by hand, and far clearer to read in a formula audit.
Try it: interactive demo
Enter a comma-separated list of numbers to see the sum of squared deviations from the mean.
Practice workbook
Frequently asked questions
What does DEVSQ actually calculate?
How is DEVSQ related to variance?
VAR.S equals DEVSQ()/(n−1) and VAR.P equals DEVSQ()/n. So DEVSQ is the numerator both variance formulas share.How is DEVSQ different from AVEDEV?
Does DEVSQ ignore text and blank cells?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class