In this tutorial, you will learn how to calculate SST, SSR, and SSE in Excel.
In order to assess how well the regression line truly fits the data, we frequently utilise three distinct sums of squares values:
1. Sum of Squares Total (SST) – The sum of squares total, denoted SST, is the squared differences between the observed dependent variable and its mean.
SST = Σ(yi – y)2
2. Sum of Squares Regression (SSR) – The second term is the sum of squares due to regression, or SSR. It is the sum of the differences between the predicted value and the mean of the dependent variable.
SSR = Σ(ŷi – y)2
3. Sum of Squares Error (SSE) – The error is the difference between the observed value and the predicted value.
SSE = Σ(ŷi – yi)2
These three measurements are related in the way that follows:
SST = SSR + SSE
Once ready, we’ll get started by utilizing real-world examples to show you how to calculate SST, SSR, and SSE in Excel.
Table of Contents
Calculate SST, SSR, and SSE in Excel
Before we begin we will need a group of data to be used to calculate SST, SSR, and SSE in Excel.
Step 1
First, you need to have a clean and tidy group of data to work with.
Step 2
To find the SST, SSR, and SSE we will select the entire data group, click ‘Data’ and click ‘Data Analysis’. A pop-up box will then appear and we will need to press ‘Regression’ and press ‘OK’.
Step 3
We will then enter the data needed to return the things we want to find.
Step 4
Once we press ‘OK’ the regression summary output will appear. The SS column in the ANOVA table displays the three sum of squares metrics SST, SSR, and SSE.
SST: 401.6
SSR: 367.12
SSE: 34.48
