This function calculates the sum of squared deviations of data points from their sample mean. It measures the total variability (dispersion) within a dataset.
Syntax
DEVSQ(number1; [number2]; …)
Arguments
- number1 (required): The first numerical value or range.
- number2, … (optional): Additional values or ranges (up to 255 arguments in modern Excel).
- Note: You can use a single array (e.g., A2:A10) instead of comma-separated arguments.
Background
Regression & Correlation
- Correlations between variables are quantified using coefficients (e.g., Pearson’s *r*).
- Regression models predict a dependent variable (*y*) from an independent variable (*x*). A linear model assumes a straight-line relationship (e.g., « as *x* increases, *y* increases/decreases »).
- Model quality is often assessed using R² (the proportion of variance explained by the model).
Forecast Error & Variability
- The mean (average) is the simplest predictor of *y*. Deviations from the mean are called forecast errors.
- Variance (calculated with VAR.S()) averages these squared deviations.
- DEVSQ() sums these squared deviations, providing a raw measure of total variability.
Formula
Where:
- xi = Individual data points.
- xˉ = Sample mean (AVERAGE of the data).
Example
Scenario: Analyzing the relationship between website visits (*x*) and online orders (*y*).
- Goal: Calculate the total squared deviations of website visits from their mean to assess data spread.
- Result: DEVSQ () returns 1,109,624,270 (see Figure below), matching the manual sum of squared deviations.
Why Use DEVSQ()?
- Regression Analysis: Used in calculating SST (Total Sum of Squares) for R².
- Statistical Tests: Helps evaluate data dispersion (e.g., ANOVA).
- Quality Control: Monitors variability in processes.
Key Notes
- Difference from VAR.S():
- VAR.S() divides DEVSQ() by n−1 to compute sample variance.
- DEVSQ() provides the numerator of variance formulas.
- Units: Output is in squared units of the original data (e.g., « visits² »).