For a supplied set of values, the Excel DEVSQ function calculates the sum of the squared deviations from the sample mean.
The syntax of the function is:
where the number arguments are one or more numeric values (or arrays of numeric values) for which you want to calculate the sum of the squared deviation.
If you are using a recent version of Excel (Excel 2007 or a later), you can enter up to 255 number arguments to the function. However, in Excel 2003, the Devsq function can only accept up to 30 number arguments.
The above 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 sum of the squared deviations is calculated in cell B1 of the spreadsheet. As shown in the formula bar, the formula for this is:
As shown in cell B1, the sum of squared deviations of the values in cells A1 - A6 is calculated as 47.5
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
For further information and examples of the Excel Devsq function, see the Microsoft Office website.
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 Devsq 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 of the number arguments that are supplied directly to the Devsq function are text strings that cannot be interpreted as numbers. |