You can use the LINEST function in Excel to fit a 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:

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.

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 R² 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 R² value, which represents the proportion of the variance in the response variable that can be explained the predictor variable.

The value for R² can range from 0 to 1.

Since the R² 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:

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