DEVSQ function

DEVSQ returns the sum of squared deviations from the mean, measuring how spread out values are from their average.

=DEVSQ(number1, ...)

Generate a DEVSQ formula

Describe what you need. The generator will reach for DEVSQ where DEVSQ is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How DEVSQ reads its arguments
number1requiredDEVSQ →
ArgumentRequiredDescription
number1RequiredA required number or cell reference; text and logical values produce #VALUE! error.
...RepeatingOptional additional numbers or ranges to include in the deviation calculation; all values must be numeric.

Returns

Returns a number representing the sum of squared deviations.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Measure consistency of Math quiz scores

StudentSubjectAssignmentScore
AliceMathQuiz92
BobMathQuiz78
CharlieMathQuiz95
=DEVSQ(92, 78, 95)

Result: 164.67

The three students' Math quiz scores average 88.33. DEVSQ squares each deviation from the mean (3.67² + 10.33² + 6.67²) and sums them to 164.67. The larger result indicates substantial variation—Bob's low score pulls the group average down significantly.

2. Assess one student's performance consistency

StudentSubjectAssignmentScore
AliceMathQuiz92
AliceMathMidterm88
AliceMathFinal95
=DEVSQ(92, 88, 95)

Result: 24.67

Alice's three Math scores average 91.67. Her deviations from that mean (0.33, −3.67, 3.33) squared and summed yield 24.67. This lower DEVSQ shows Alice has consistent performance, fluctuating only slightly around her personal average.

3. Compare score variation between subjects

StudentSubjectAssignmentScore
AliceEnglishQuiz88
BobEnglishQuiz85
CharlieEnglishQuiz92
=DEVSQ(88, 85, 92)

Result: 24.67

English quiz scores produce DEVSQ = 24.67, while Math quizzes (from Example 1) give 164.67. Comparing these shows English grades cluster tightly around their mean, whereas Math grades are more dispersed—useful for identifying which subject has more variability.

Common errors

Which DEVSQ error are you seeing?
DEVSQ returned an error#VALUE!
Include only numeric values: use =DEVSQ(92, 78, 95) instead of =DEVSQ("Alice", 92, 78, 95).
#REF!
Recreate the formula using current cell addresses, or undo the deletion before constructing the formula.
#NAME?
Verify the exact spelling: =DEVSQ(). Check your spreadsheet application's available functions if the error persists.
ErrorWhy it happensHow to fix it
#VALUE!A range contains text that cannot be converted to a number, such as a student name or column header mixed into the data range.Include only numeric values: use =DEVSQ(92, 78, 95) instead of =DEVSQ("Alice", 92, 78, 95).
#REF!The formula references cells that have been deleted or moved, breaking the cell links.Recreate the formula using current cell addresses, or undo the deletion before constructing the formula.
#NAME?The function name is misspelled or not recognized, such as =DEVSQR() or =DEV_SQ().Verify the exact spelling: =DEVSQ(). Check your spreadsheet application's available functions if the error persists.

Tips and when to use something else

  • DEVSQ is the numerator of variance: divide DEVSQ by the count for population variance, or by (count − 1) for sample variance.
  • Use DEVSQ to spot outliers and inconsistency; higher values signal data spread further from the mean.
  • If you need the standard deviation directly, use dedicated functions like STDEV instead; DEVSQ is best for intermediate statistical calculations.
  • DEVSQ ignores empty cells and treats zeros as valid numbers; exclude non-numeric values before using the function.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.COUNTCOUNT counts the number of cells containing numeric values and dates in one or more ranges, ignoring text and blank cells.MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.MINMIN finds and returns the smallest numeric value in a range or list, automatically excluding text and empty cells.

Frequently asked questions

What's the difference between DEVSQ and SUMPRODUCT with squared differences?
DEVSQ automatically calculates the mean and squares deviations, making it simpler. SUMPRODUCT((range − AVERAGE(range))^2) produces the same result but requires more setup. Use DEVSQ for clarity and conciseness.
Can I use DEVSQ to calculate variance?
Yes. Variance equals DEVSQ divided by the count: =DEVSQ(range) / COUNT(range) for population variance, or =DEVSQ(range) / (COUNT(range) − 1) for sample variance. However, VAR.P and VAR.S functions do this automatically.
Why does DEVSQ return zero with identical values?
If all values are the same, each deviation from the mean is zero. Squaring zero gives zero, so DEVSQ of identical numbers is always 0. This is correct: no deviation means no spread.
Does DEVSQ work with negative numbers?
Yes. Negative numbers are treated like any other value. Deviations are squared regardless of sign, so the result is always non-negative. For example, −5 and +5 both contribute equally to DEVSQ.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17