How to use DEVSQ Function in Excel

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:

  1. Number1 (required argument) – This is the value for which we wish to calculate the sum of squared deviations.
  2. 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:

DEVSQ Function in Excel 1 - How to use DEVSQ Function in 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

DEVSQ Function - How to use DEVSQ Function in Excel

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:

READ:  How to use PERMUT Function in Excel

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:
    DEVSQ Function in Excel - How to use DEVSQ Function in Excel

Add a Comment

Your email address will not be published. Required fields are marked *