You can use the LINEST function to fit a regression model in Excel.
This function uses the following basic syntax:
LINEST(known_y's, known_x's, const, stats)
where:
- known_y’s: A column of values for the response variable
- known_x’s: One or more columns of values for the predictor variables
- const: TRUE = calculate intercept normally, FALSE = force intercept to be zero
- stats: TRUE = calculate additional regression statistics, FALSE = only calculate coefficients
The following example shows how to interpret the output of the LINEST function in practice.
Example: How to Interpret LINEST Output in Excel
Suppose we have the following dataset that contains one predictor variable (x) and one response variable (y):
We can type the following formula into cell D1 to perform simple linear regression with this dataset:
=LINEST(A2:A15, B2:B15, TRUE, TRUE)
The output from the LINEST function contains the coefficients of the regression model along with several additional statistics:
The following screenshot provides an explanation of each value in the output:
From the output we can see:
- The coefficient for β0 is 3.52169.
- The coefficient for β1 is 0.693717.
Using these values, we can write the equation for this simple regression model:
y = 3.52169 + 0.693717(x)
We interpret this to mean that each additional one unit increase in the predictor variable, x, is associated with an average increase of 0.693717 in the response variable, y.
We interpret the intercept term to mean that when x is equal to zero, the average value of y is 3.52169.
We can also see:
- The R-squared value of the model is 0.888.
We interpret this to mean that the predictor variable is able to explain 88.8% of the variation in the response variable of the model.
Related: What is a Good R-squared Value?
Additional Resources
The following tutorials provide additional information on regression in Excel:
How to Interpret P-Values in Regression Output in Excel
How to Add a Regression Line to a Scatterplot in Excel
How to Perform Polynomial Regression in Excel
