DEVSQ in Google Sheets: Sum Squared Deviations and Check Variance

• Introduced: first service launch date is not established by the official history reviewed; no numbered desktop introduction is assigned.
• Availability: documented in the rolling Google Sheets service. Examples target ordinary web-spreadsheet cells; mobile clients and imported file modes are not separately certified.
• Checked: October 14, 2026; official DEVSQ reference. Documentation and independently calculated examples, without native Sheets execution.

=DEVSQ(value1, [value2, ...])

Provide one value or range and optional additional values or ranges. Google explicitly ignores text found inside referenced ranges. It also states that Sheets supports an arbitrary number of arguments despite an older 30-argument specification; this is documentation scope, not a tested performance limit.

DEVSQ returns a sum in squared measurement units. It does not divide by the observation count or count minus one. Those divisions distinguish population and sample variance.

Enter numeric 3, 7, 7 and 11 in A2:A5. Put =AVERAGE(A2:A5) in D2, with expected result 7. In B2 enter and fill down:

=(A2-$D$2)^2

Expected B2:B5 values are 16, 0, 0 and 16. Compare:

=SUM(B2:B5)
=DEVSQ(A2:A5)

Both expected results are 32. The helper column exposes the center being subtracted; simply squaring the original measurements would answer a different question.

=SUMSQ(A2:A5)
=DEVSQ(A2:A5)

Expected results are 228 and 32. The raw squares are 9, 49, 49 and 121. In exact arithmetic, subtracting four times the squared mean, 4×49, from 228 leaves 32.

That identity explains the difference but is not a recommendation to use a cancellation-prone shortcut on very large, nearly equal values. Use the centered calculation and inspect numerical conditioning when precision matters. See SUMSQ for raw squared totals.

For the four original observations:

=DEVSQ(A2:A5)/COUNT(A2:A5)
=DEVSQ(A2:A5)/(COUNT(A2:A5)-1)

Expected results are 8 and 10.666666667, approximately. The first is the population-variance formula; the second is the sample-variance formula. Choosing a denominator is a modeling decision about the data, not a formatting option.

Google explicitly relates DEVSQ to (COUNT(range)-1)*VAR(range) for the same sample. For this range, sample variance 32/3 multiplied by three returns 32. That relationship needs at least two observations for the ordinary sample-variance definition.

On a separate sheet, add the text Pending after 3, 7, 7 and 11. Referencing the five-cell range still has expected DEVSQ 32 under the documented range-text rule.

Replace Pending with numeric 0. The mean becomes 5.6 and the expected centered-square sum becomes 71.2. The zero is a real fifth observation, not an ignored label.

=DEVSQ(7)

Google documents expected result 0 when fewer than two values are supplied. A single observation has no deviation from its own mean. This does not make DEVSQ(range)/(COUNT(range)-1) a valid sample variance for one observation: that expression divides by zero.

For translated values 13, 17, 17 and 21, expected DEVSQ remains 32. Every value and the mean move by the same amount.

For scaled values −6, −14, −14 and −22, expected DEVSQ is 128. Multiplication by −2 multiplies squared distances by four, not by −2. The result cannot be negative for these real numeric inputs.

For a separate 3, 7, 7 and 31 dataset, mean 12 gives squared deviations 81, 25, 25 and 361, totaling 492. The large departure has greater influence under squaring than under AVEDEV, whose expected result for that dataset is 9.5.

The documented fewer-than-two rule does not establish every no-argument, blank-only or error-cell parser case. Direct text arguments, numeric text and Booleans are not assigned imported Excel coercion rules here. These calculations use explicit numeric observations and the separately stated range-text control.

Record the range and count with a reported sum of squares. A zero result from one observation does not establish a stable process or a sufficiently large sample.

• Google DEVSQ help.
• Editorial testing standards.

Inputs are fictional. Approximate answers are independent mathematical expectations rounded to nine places, not captured native results. Complete type/error matrices, extreme-magnitude accuracy and cross-client behavior remain untested.