21.9 C
London
Tuesday, July 22, 2025
HomeSoftware TutorialsExcelExcel: How to Use LINEST to Perform Multiple Linear Regression

Excel: How to Use LINEST to Perform Multiple Linear Regression

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

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

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories