Multiple Linear Regression with Linest Function

This post demonstrates the usage of the Excel LINEST function to perform multiple linear regression. The data being utilized are tabulated below. It is a subset of the data used in the final course project.

Sample Data

The data shown below is the top 5 rows of 30 rows LINEST operated on for this explanation.

Age  Years at Employer Years at Address Income Credit Card Debt  Automobile Debt Outcome
26 0.35 0.24 $12,181 ($2,057) ($3,696) 1
28 4.47 0.37 $38,536 ($6,970) ($3,018) 1
37 9.30 0.02 $30,602 ($2,892) ($1,674) 0
28 8.43 1.06 $15,588 ($38) ($1,758) 0
39 5.40 0.74 $27,599 ($776) ($4,374) 0

The Output

The table below contains the formatted output of the LINEST function.

0.00 0.00 0.00 -0.04 -0.05 0.00 0.61
0.00 0.00 0.00 0.13 0.02 0.01 0.41
0.513 0.376 #N/A #N/A #N/A #N/A #N/A
4.04 23 #N/A #N/A #N/A #N/A #N/A
3.42 3.25 #N/A #N/A #N/A #N/A #N/A

Columns having coefficients and standard errors of zero have high collinearity and therefore add very little to the model.

Key to the Output

The table below contains a key for interpreting the output of the LINEST function.

x_6 Coeff x_5 Coeff x_4 Coeff x_3 Coeff x_2 Coeff x_1 Coeff Constant
x_6 Std Err x_5 Std Err x_4 Std Err x_3 Std Err x_2 Std Err x_1 Std Err Constant Std Err
R^2 SDev(y) #N/A #N/A #N/A #N/A #N/A
F-Statistic Degrees of Freedom #N/A #N/A #N/A #N/A #N/A
“Regression Sum of Squares” “Residual Sum of Squares” #N/A #N/A #N/A #N/A #N/A

The x_6 Coeff is the coefficient associated with the “Automobile Debt” column.

Instructions

LINEST works differently from many excel functions. Instructions for using it follow. The data are the data described above, with a single header row in row A of the spreadsheet.

LINEST takes the following parameters: LINEST([known_ys], [known_xs], const=true, stats=true)

[known_ys]: the outcome column, G2:G31
[known_xs]: the left six columns, A2:F31
const=true: if omitted, Excel will assume true. If false, then the constant term (y-intercept) will be set to 0.
stats=true: if omitted, Excel will assume true. If false, then the extra stats in rows 3 through 5 of the output will not be included.

  1. Enter the LINEST function in a cell, according to the explanation set forth above.
  2. Select a 5-row, n-column group of cells with the LINEST function cell in the top left, where n is the total number of columns of data, including both independent and dependent columns.
  3. Press Control-U.
  4. Press Control-Shift-Return.

Note: the commands in step 3 and step 4 are for Excel for Mac only. Other versions will differ.


Some content from this note was taken from the spreadsheets listed below. They are distributed as part of the Mastering Data Analysis in Excel course on coursera.org, and licensed by Daniel Egger under CC BY-NC 4.0.

  • Excel-Linest-Function.xlsx

Some other content is taken from my notes on the Coursera course “Mastering Data Analysis in Excel.” It is sponsored by Duke University and the course content is presented by Professor Daniel Egger.