How to Use DEVSQ in Google Sheets (With Example)

You can use the DEVSQ function in Google Sheets to calculate the sum of squares of deviations for a given sample.

This function uses the following basic syntax:

=DEVSQ(value1, value2, value3, ...)

Here’s the formula that DEVSQ actually uses:

Sum of squares of deviations = Σ(x_i – x)²

where:

• x_i: The i^th data value
• x: The sample mean

The following example shows how to use this function in practice.

Example: How to Use DEVSQ in Google Sheets

Suppose we have the following dataset in Google Sheets:

We can use the following formula to calculated the sum of squares of deviations for this dataset:

=DEVSQ(A2:A13)

The following screenshot shows how to use this formula in practice:

The sum of squares of deviations turns out to be 319.

We can confirm this is correct by manually calculating the sum of squares of deviations for this dataset.

Note: The average value of this dataset is 9.5.

Knowing this, we can simply plug in the values from the dataset into the formula for sum of squares of deviations:

• Sum of squares of deviations = Σ(x_i – x)²
• Sum of squares of deviations = (2-9.5)² + (3-9.5)² + (5-9.5)² + (5-9.5)² + (7-9.5)² + (8-9.5)² + (9-9.5)² + (12-9.5)² + (14-9.5)² + (15-9.5)² + (16-9.5)² + (18-9.5)²
• Sum of squares of deviations = 56.25 + 42.25 + 20.25 + 20.25 + 6.25 + 2.25 + 0.25 + 6.25 + 20.25 + 30.25 + 42.25 + 72.25
• Sum of squares of deviations = 319

The sum of squares of deviations turns out to be 319.

This matches the value that we calculated using the DEVSQ function.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

How to Use COUNTIF with Multiple Ranges in Google Sheets
How to Use SUMIF with Multiple Columns in Google Sheets
How to Sum Across Multiple Sheets in Google Sheets