In this article, we explain how to calculate the Sxx value in Excel. The Sxx value refers to the sum of squares of deviations due to X. It can be calculated using the formula,
Sxx = ∑(Xi – X̅)2
In order to calculate Sxx 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 Sxx.
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
The mean of the data values terms out to be 7.25.
Step 2: Calculate the Square of the Deviations
We calculate the (Xi – X̅)2 values in the column B.
Enter the following formula in column B2,
This will calculate the value (X1 – 7.25)2 = 39.0625.
Drag the cursor down up to ROW 9 to calculate all the square deviation values.
Step 3: Find Sxx by Summing the Values
We have found all the (Xi – X̅)2 values in the column B, from cell B2 to cell B9. Now we add up all these values to calculate the value of Sxx.
Go to cell D5 and type the following command,
We obtain the Sxx value in cell D5. We conclude that,
Sxx = 259.5