DEVSQ Function (LibreOffice Calc)
The DEVSQ function returns the sum of squared deviations of a dataset from its mean. It is used in statistical analysis, variance calculations, and data dispersion modeling.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✔ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the DEVSQ Function Does ▾
- Computes the sum of squared deviations from the mean
- Measures spread or dispersion of data
- Forms the basis of variance and standard deviation
- Works with numbers, ranges, and mixed references
Syntax ▾
DEVSQ(number1; [number2]; ...)
Arguments
- number1, number2, …:
One or more numeric values, ranges, or references.
Basic Examples ▾
Sum of squared deviations for a range
=DEVSQ(A1:A5)
Multiple arguments
=DEVSQ(A1:A5; B1:B5)
With literal numbers
=DEVSQ(1; 2; 3; 4; 5)
Advanced Examples ▾
Relationship to variance
Sample variance:
=DEVSQ(A1:A10) / (COUNT(A1:A10) - 1)
Population variance:
=DEVSQ(A1:A10) / COUNT(A1:A10)
Compute standard deviation manually
=SQRT(DEVSQ(A1:A10) / (COUNT(A1:A10) - 1))
Weighted squared deviations (manual)
=SUMPRODUCT((A1:A10 - AVERAGE(A1:A10))^2; B1:B10)
Combine with FILTER for conditional dispersion
=DEVSQ(FILTER(A1:A100; B1:B100="North"))
Use in regression residual analysis
=DEVSQ(A1:A20 - predicted_values)
Edge Cases and Behavior Details ▾
DEVSQ returns a number
Behavior details
- Ignores text and empty cells
- Errors propagate
- Boolean values are ignored
- Requires at least one numeric value
- Equivalent to:
[ \sum (x_i - \bar{x})^2 ]
Invalid input → Err:502
Common Errors and Fixes ▾
Err:502 — Invalid argument
Cause:
- No numeric values
- Invalid references
- Arrays containing errors
Fix:
- Clean data
- Wrap with IFERROR or FILTER
Unexpectedly large values
Cause:
- Squaring amplifies outliers
Fix:
- Inspect dataset for anomalies
Best Practices ▾
- Use DEVSQ when you need raw dispersion, not normalized variance
- Combine with COUNT for manual variance calculations
- Use STDEV.S or VAR.S for standard statistical outputs
- Clean data before applying DEVSQ
- Use FILTER to compute dispersion on subsets
DEVSQ is the backbone of variance and standard deviation — mastering it gives you full control over custom statistical modeling.
Related Patterns and Alternatives ▾
- VAR / VAR.S / VAR.P — variance
- STDEV / STDEV.S / STDEV.P — standard deviation
- SUMSQ — sum of squares (not mean‑adjusted)
- AVERAGE — mean
- COUNT — sample size
By mastering DEVSQ, you can build precise, custom statistical and analytical models in LibreOffice Calc.