# DEVSQ Function in Excel

### Part 1: Introduce

#### Definition

The DEVSQ function in Excel calculates the sum of squares of deviations of data points from their sample mean.

#### Purpose

The function is used to measure the total variation in the dataset. This can be useful in statistical analysis to understand the dispersion of data points.

#### Syntax & Arguments

syntax
```DEVSQ(number1, [number2], ...) ```
• number1, number2, …: Number1 is required. Subsequent numbers are optional. 1 to 255 arguments for which you want to calculate the sum of squared deviations. You can also use a single array or a reference to an array instead of arguments separated by commas.

#### Explain the Arguments in the Function

• number1, number2, …: These are the observed values of the variable you are trying to analyze. They can be individual numbers, arrays, or references that contain numbers.

#### Return Value

The DEVSQ function returns the sum of squares of deviations of data points from their sample mean.

#### Remarks

• Arguments can be numbers or names, arrays, or references containing numbers.
• Logical values and text representations of numbers 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 zero values are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.

### Part 2: Examples

#### Example 1

• Purpose of Example: To calculate the sum of squares of deviations for sales data.
• Data Tables and Formulas:
ABC
1SalesDEVSQResult
2100=DEVSQ(A2:A4)20000
3200
4300
• Explanation: The DEVSQ function calculates the sum of squares of deviations for the sales data in column A. This can be useful for understanding the dispersion of sales data. The result is displayed in column C.

#### Example 2

• Purpose of Example: To calculate the sum of squares of deviations for customer satisfaction scores.
• Data Tables and Formulas:
ABC
1SatisfactionDEVSQResult
270=DEVSQ(A2:A4)200
380
490
• Explanation: In column A, the DEVSQ function calculates the sum of squares of deviations for the customer satisfaction scores. This can be useful in understanding the dispersion of satisfaction scores. The result is displayed in column C.

#### Example 3

• Purpose of Example: To calculate the sum of squares of deviations for website traffic data.
• Data Tables and Formulas:
ABC
1TrafficDEVSQResult
21000=DEVSQ(A2:A4)2000000
32000
43000
• Explanation: In column A, the DEVSQ function calculates the sum of squares of deviations for the website traffic data. This can be useful for understanding the dispersion of traffic data. The result is displayed in column C.

#### Example 4

• Purpose of Example: To calculate the sum of squares of deviations for product ratings.
• Data Tables and Formulas:
ABC
1RatingDEVSQResult
24=DEVSQ(A2:A4)2
35
45
• Explanation: In column A, the DEVSQ function calculates the sum of squares of deviations for the product ratings. This can be useful in understanding the dispersion of product ratings. The result is displayed in column C.

#### Example 5

• Purpose of Example: To calculate the sum of squares of deviations for employee performance scores.
• Data Tables and Formulas:
ABC
1PerformanceDEVSQResult
285=DEVSQ(A2:A4)50
390
495
• Explanation: In column A, the DEVSQ function calculates the sum of squares of deviations for the employee performance scores. This can be useful in understanding the dispersion of performance scores. The result is displayed in column C.

#### Example 6

• Purpose of Example: To calculate the sum of squares of deviations for sales data only if the sales are above a certain amount.
• Data Tables and Formulas:
ABCD
1SalesDEVSQMessageResult
2100=DEVSQ(A2:A4)=IF(A2>150, B2, “N/A”)20000
3200=DEVSQ(A2:A4)=IF(A3>150, B3, “N/A”)20000
4300=DEVSQ(A2:A4)=IF(A4>150, B4, “N/A”)20000
• Explanation: The DEVSQ function calculates the sum of squares of deviations for the sales data in column A, but only for rows where the sales are above 150. For the first row, where the sales are 100, the function returns “N/A”. The result is displayed in the last column.

#### Example 7

• Purpose of Example: To calculate the sum of squares of deviations for customer satisfaction scores.
• Data Tables and Formulas:
ABCD
1SatisfactionDEVSQSumResult
270=DEVSQ(A2:A4)=SUM(B2:B4)200
380=DEVSQ(A2:A4)=SUM(B2:B4)200
490=DEVSQ(A2:A4)=SUM(B2:B4)200
• Explanation: The SUM function is used to calculate the total of the sum of squares of deviations calculated in column B. The total sum of squares of deviations is 600. The result is displayed in the last column.

#### Example 8

• Purpose of Example: To calculate the sum of squares of deviations for website traffic data and look up the corresponding website name.
• Data Tables and Formulas:
ABCDE
1TrafficWebsite IDDEVSQWebsite NameResult
210001=DEVSQ(A2:A4)=VLOOKUP(B2, G2:H4, 2, FALSE)2000000
320002=DEVSQ(A2:A4)=VLOOKUP(B3, G2:H4, 2, FALSE)2000000
430003=DEVSQ(A2:A4)=VLOOKUP(B4, G2:H4, 2, FALSE)2000000
GH
1Website IDWebsite Name
21Website A
32Website B
43Website C
• Explanation: The VLOOKUP function finds the website name corresponding to the website ID in column B. The DEVSQ function calculates the sum of squares of deviations for the website traffic data in column A, and the corresponding website name is displayed in column D. The result is shown in the last column.

#### Example 9

• Purpose of Example: To calculate the sum of squares of deviations for product ratings and round the result to the nearest integer.
• Data Tables and Formulas:
ABCD
1RatingDEVSQRounded ResultResult
24=DEVSQ(A2:A4)=ROUND(B2, 0)2
35=DEVSQ(A2:A4)=ROUND(B3, 0)2
45=DEVSQ(A2:A4)=ROUND(B4, 0)2
• Explanation: The ROUND function rounds the sum of squares of deviations to the nearest integer. This could be useful in scenarios where the sum of squares of deviations needs to be a whole number, such as predicting the number of items sold or the number of people attending an event. The result is displayed in the last column.

#### Example 10

• Purpose of Example: To calculate the sum of squares of deviations for employee performance scores and display a message based on the result.
• Data Tables and Formulas:
ABCDE
1PerformanceDEVSQMessageResult
285=DEVSQ(A2:A4)=IF(B2>50, “High Variance”, “Low Variance”)50
390=DEVSQ(A2:A4)=IF(B3>50, “High Variance”, “Low Variance”)50
495=DEVSQ(A2:A4)=IF(B4>50, “High Variance”, “Low Variance”)50
• Explanation: The IF function is used to display a message based on the result of the DEVSQ function. If the sum of squares of deviations is greater than 50, it says “High Variance”. Otherwise, it displays “Low Variance”. This could be useful in scenarios where you want to quickly identify whether the variance in a dataset is high or low. The result is displayed in the last column.

#### Example 11

• Purpose of Example: To calculate the sum of squares of deviations for a set of data, and then calculate the average of these sums.
• Data Tables and Formulas:
ABCD
210=DEVSQ(A2:A4)=AVERAGE(B2:B4)0
320=DEVSQ(A2:A4)=AVERAGE(B2:B4)0
430=DEVSQ(A2:A4)=AVERAGE(B2:B4)0
• Explanation: The AVERAGE function is used to calculate the average of the sum of squares of deviations. This could be useful in scenarios where you want to understand the average dispersion in a dataset. The result is displayed in the last column.

#### Example 12

• Purpose of Example: To calculate the sum of squares of deviations for a data set and then find the maximum of these sums.
• Data Tables and Formulas:
ABCD