*36*

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 (y_{i}) and the mean of the response variable (y).

- SST = Î£(y
_{i}â€“ 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 (y_{i}).

- SSE = Î£(Å·
_{i}â€“ y_{i})^{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.

**Additional Resources**

How to Perform Simple Linear Regression in Excel

How to Perform Multiple Linear Regression in Excel

How to Perform Polynomial Regression in Excel

How to Perform Exponential Regression in Excel