Excel: How to Use LINEST to Perform Multiple Linear Regression


You can use the LINEST function in Excel to fit a multiple linear regression model to a dataset.

This function uses the following basic syntax:

=LINEST(known_y's, [known_x's], [const], [stats])

where:

  • known_y’s: An array of known y-values
  • known_x’s: An array of known x-values
  • const: Optional argument. If TRUE, the constant b is treated normally. If FALSE, the constant b is set to 1.
  • stats: Optional argument. If TRUE, additional regression statistics are returned. If FALSE, additional regression statistics are not returned.

The following step-by-step example shows how to use this function in practice.

Step 1: Enter the Data

First, let’s enter the following dataset in Excel:

Step 2: Use LINEST to Fit Multiple Linear Regression Model

Suppose we would like to fit a multiple linear regression model using x1, x2, and x3 as predictor variables and y as the response variable.

To do so, we can type the following formula into any cell to fit this multiple linear regression model

=LINEST(D2:D14, A2:C14)

The following screenshot shows how to use this formula in practice:

Excel LINEST multiple linear regression

Here’s how to interpret the output:

  • The coefficient for the intercept is 28.5986.
  • The coefficient for x1 is 0.34271.
  • The coefficient for x2 is -3.00393.
  • The coefficient for x3 is 0.849687.

Using these coefficients, we can write the fitted regression equation as:

y = 28.5986 + 0.34271(x1) – 3.00393(x2) + 0.849687(x3)

Step 3 (Optional): Display Additional Regression Statistics

We can also set the value for the stats argument in the LINEST function equal to TRUE to display additional regression statistics for the fitted regression equation:

The fitted regression equation is still the same:

y = 28.5986 + 0.34271(x1) – 3.00393(x2) + 0.849687(x3)

Here’s how to interpret the other values in the output:

  • The standard error for x3 is 0.453295.
  • The standard error for x2 is 1.626423.
  • The standard error for x1 is 1.327566.
  • The standard error for the intercept is 13.20088.
  • The R2 for the model is .838007.
  • The residual standard error for y is 3.707539.
  • The overall F-statistic is 15.51925.
  • The degrees of freedom is 9.
  • The regression sum of squares is 639.9797.
  • The residual sum of squares is 123.7126.

In general, the most interesting metric in these additional statistics is the R2 value, which represents the proportion of the variance in the response variable that can be explained the predictor variable.

The value for R2 can range from 0 to 1.

Since the R2 for this particular model is .838, it tells us that the predictor variables do a good job of predicting the value of the response variable y.

Related: What is a Good R-squared Value?

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Use LOGEST Function in Excel
How to Perform Nonlinear Regression in Excel
How to Perform Cubic Regression in Excel

Leave a Reply

Your email address will not be published.