Home Â» How to Calculate SST, SSR, and SSE in Excel

# How to Calculate SST, SSR, and SSE in Excel

We often use three different sum of squares values to measure how well a regression line actually fits a dataset:

1. Sum of Squares Total (SST) â€“Â The sum of squared differences between individual data points (yi) and the mean of the response variable (y).

• SST = Î£(yi â€“ y)2

2. Sum of Squares Regression (SSR) â€“ The sum of squared differences between predicted data points (Å·i) and the mean of the response variable(y).

• SSR = Î£(Å·i â€“ y)2

3. Sum of Squares Error (SSE) â€“ The sum of squared differences between predicted data points (Å·i) and observed data points (yi).

• SSE = Î£(Å·i â€“ yi)2

The following step-by-step example shows how to calculate each of these metrics for a given regression model in Excel.

### Step 1: Create the Data

First, letâ€™s create a dataset that contains the number of hours studied and exam score received for 20 different students at a certain school:

### Step 2: Fit a Regression Model

Along the top ribbon in Excel, click the DataÂ tab and click onÂ Data Analysis.Â If you donâ€™t see this option, then you need to first install the free Analysis ToolPak.

Once you click onÂ Data Analysis,Â a new window will pop up. SelectÂ RegressionÂ and click OK.

In the new window that appears, fill in the following information:

Once you clickÂ OK, the regression output will appear.

### Step 3: Analyze the Output

The three sum of squares metrics â€“ SST, SSR, and SSE â€“ can be seen in theÂ SS column of theÂ ANOVA table:

The metrics turn out to be:

• Sum of Squares Total (SST): 1248.55
• Sum of Squares Regression (SSR): 917.4751
• Sum of Squares Error (SSE): 331.0749

We can verify that SST = SSR + SSE:

• SST = SSR + SSE
• 1248.55 = 917.4751 + 331.0749

We can also manually calculate the R-squared of the regression model:

• R-squared = SSR / SST
• R-squared = 917.4751 / 1248.55
• R-squared = 0.7348

This tells us thatÂ 73.48% of the variation in exam scores can be explained by the number of hours studied.