For a supplied set of values, the Excel DEVSQ function calculates the sum of the squared deviations from the sample mean.
Syntax: DEVSQ( number1, [number2], … )
where the arguments, number1, [number2], etc, are one or more numerical values or references to cells containing numbers. If you are using Excel 2007 or Excel 2010, you can enter up to 255 number arguments to the function. However, in Excel 2003, the function can only accept up to 30 number arguments.
Devsq Function Example
The spreadsheet on the right shows the Excel Devsq function, used to calculate the sum of squared deviations of the set of values in cells A1 – A6. The standard deviation of the values is calculated in cell B1 of the spreadsheet. The formula for this, as shown in the formula bar, is :
As shown in cell B1, the sum of squared deviations of the values in cells A1 – A6 is 47.5
Other Argument Types
In the example above, the arguments to the Devsq function are input as a cell range. However, you can also input figures directly, as individual numbers or number arrays. For example, if you wanted to add the values 8 and 10 to the data array in cells A1 – A6, you could add these directly as follows:
Either as individual numbers:
Or, as an array of numbers:
This gives the updated result 79.875
Devsq Function Common Errors
If you get an error from the Excel Devsq function this is likely to be one of the following:
|#NUM!||–||Occurs if none of the values supplied to the function are numeric.
(Note that text representations of numbers, that are supplied as a part of an array, are not interpreted as numeric values by the Devsq function)
|#VALUE!||–||Occurs if any values that are supplied directly to the Devsq function are text values that cannot be interpreted as numeric values.|