How to Calculate Sxx in Excel (with Examples)

In this article, we explain how to calculate the S_xx value in Excel. The S_xx value refers to the sum of squares of deviations due to X. It can be calculated using the formula,

S_xx = ∑(X_i – X̅)²

In order to calculate S_xx in excel we first calculate the mean of the dataset using the =AVERAGE(Range) function where Range is the range of the data values.

We then take the difference of the data values from the mean and square them. The sum of the resulting values is equal to S_xx.

Step 1: Enter the Data Values and find the Mean

Suppose that the given data values are: 1, 1, 2, 6, 8, 9, 13, and 18. We enter the data values into column A as shown below.

Calculate the mean of the data value in cell D2 using the command

=AVERAGE(A2:A9).

The mean of the data values terms out to be 7.25.

Step 2: Calculate the Square of the Deviations

We calculate the (X_i – X̅)² values in the column B.

Enter the following formula in column B2,

=(A2-$D$2)^2

This will calculate the value (X₁ – 7.25)² = 39.0625.

Drag the cursor down up to ROW 9 to calculate all the square deviation values.

Step 3: Find S_xx by Summing the Values

We have found all the (X_i – X̅)² values in the column B, from cell B2 to cell B9. Now we add up all these values to calculate the value of S_xx.

Go to cell D5 and type the following command,

=SUM(B2:B9).

We obtain the Sxx value in cell D5. We conclude that,

S_xx = 259.5

Additional Resources:

1. S_xx Calculator.
2. How to Calculate S_xx by Hand.