DEVSQ in Excel: Sum Squared Deviations and Check Variance
Calculate Excel DEVSQ with transparent centered values, zero and type controls, variance denominators and translation or scaling checks.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: DEVSQ adds the squared distances of observations from their arithmetic mean. For 2, 4, 6, 8, the mean is five and the squared deviations are 9, 1, 1, 9, so the expected result is 20.
Availability and version history
- Introduced: first introduction version is not established by the official history reviewed; the current product list is not a launch record.
- Availability: official help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
- Checked: October 14, 2026; official DEVSQ reference. Documentation and independently calculated examples; no native Excel execution or broader platform certification.
Syntax and participation
=DEVSQ(number1, [number2], ...)
Microsoft allows 1–255 arguments, including values or ranges. Direct logical values and numeric text participate; referenced text, logical values and empty cells are ignored. Zero-valued cells are included. Error values or unconvertible text can cause errors, but the page does not assign every such input a specific displayed code.
The result is a sum, not a variance already divided by a count. Its unit is the square of the input unit. Use English function names and the locale-appropriate separators.
Example 1: expose the centering calculation
Enter A2:A5 as 2, 4, 6, 8. In B2 enter =A2-AVERAGE($A$2:$A$5) and fill down. In C2 enter =B2^2 and fill down.
| A · Value | Expected B · Deviation | Expected C · Square |
|---|---|---|
| 2 | -3 | 9 |
| 4 | -1 | 1 |
| 6 | 1 | 1 |
| 8 | 3 | 9 |
=DEVSQ(A2:A5)
=SUM(C2:C5)
Both expected results are 20. The signed deviations add to zero, but their squares do not cancel. Use the same fixed mean range for all rows.
Example 2: distinguish centered and uncentered squares
=SUMSQ(A2:A5)
=DEVSQ(A2:A5)
Expected results are 120 and 20. SUMSQ squares the raw values relative to zero; DEVSQ first centers them on their mean. Substituting one for the other changes the quantity being measured.
For these small numbers, =SUMSQ(A2:A5)-SUM(A2:A5)^2/4 also gives 20. That algebraic identity is a transparent control, not a recommendation for large nearly equal values, where subtracting large similar quantities can be numerically sensitive.
Advanced example: choose a variance denominator explicitly
For this four-observation dataset:
=DEVSQ(A2:A5)/4
=DEVSQ(A2:A5)/(4-1)
Expected values are 5 and approximately 6.666666667. The first is the variance of the supplied population. The second applies the sample-variance denominator of n−1.
Their square roots are approximately 2.236067977 and 2.581988897, respectively. DEVSQ supplies a common numerator; it does not decide whether the data represents a population or a sample. VAR.P and VAR.S address those separately defined statistics.
Advanced example: make zero participation visible
With numeric 2, 4, 6, the mean is four and DEVSQ is 8. Adding numeric zero gives 0, 2, 4, 6, whose mean is three and DEVSQ is 20.
Zero is an actual observation. Replacing an unavailable measurement with zero can therefore change both the center and the squared deviation total. Preserve the original status if an imputation policy is used.
Advanced example: contrast direct and referenced types
Enter numeric 2, text 4 and logical TRUE in F2:F4, using a leading apostrophe for the text:
=DEVSQ(F2:F4)
=DEVSQ(2,"4",TRUE)
The documented expected values are 0 and approximately 4.666666667. The direct participants 2, 4 and 1 have mean 7/3; their squared deviations total 14/3. The referenced control contains only one numeric participant.
A one-value zero deviation does not mean a useful variability estimate was obtained from a rich dataset. Always check how many numeric observations participated.
Translation and scale checks
Adding ten to the original observations gives 12, 14, 16, 18 and leaves DEVSQ at 20. Multiplying every observation by negative three gives -6, -12, -18, -24 and multiplies DEVSQ by nine, producing 180.
Squared deviations scale with the square of a unit conversion. A negative multiplier reverses the observations but does not produce a negative sum of squares in exact arithmetic.
Errors and numerical limits
Inspect stored types and range boundaries before hiding a discrepancy with an error fallback. The selected Microsoft page does not establish a complete native error matrix, maximum usable magnitude or accuracy for cancellation-prone data. These finite calculations verify the definitions and denominators, without claiming native execution.
References and verification
Inputs are fictional. Decimal answers are independent mathematical expectations rounded to nine places, not captured native results. Extreme magnitudes, complete coercion matrices and cross-version numerical accuracy remain untested.