DEVSQ function in excel is a statistical function. This function is used to find the sum of the squared deviations from the mean from a sample. It is a worksheet function and can be entered into the cell in which value is required.
Syntax:= DEVSQ(number1, [number2], …)
The DEVSQ function syntax has the following arguments:
- Number1 (required argument) – This is the value for which we wish to calculate the sum of squared deviations.
- Number2 (optional argument) – A second numeric value (or array of numeric values) for which we want to calculate the sum of the squared deviation.
Example: Let’s look at some Excel DEVSQ function examples and explore how to use the DEVSQ function as a worksheet function in Microsoft Excel:
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 A2 – A26
The sum of the squared deviations is calculated in cell B2 of the spreadsheet. As shown in the formula bar, the formula for this is:
Syntax: =DEVSQ(A2:A26)
Result: As shown in cell B2, the sum of squared deviations of the values in cells A2 – A26 is calculated as 45.1974
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.5, 7.25 and 6.25 to the data array in cells A2 – A26, you could add these directly as follows:
Either as individual numbers:
=DEVSQ(A2:A26,8.5,7.25,6.25)
Or, as an array of numbers:
=DEVSQ(A2:A26,{8.5,7.25,6.25})
This gives the updated result 48.09526786
Note:
- Arguments can either be numbers or names, arrays, or references that contain numbers.
- Logical values and text representations of numbers that you type directly into the list of arguments are counted.
- If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
- Arguments that are error values or text that cannot be translated into numbers cause errors.
-
The equation for the sum of squared deviations is:
