# Multiple Linear Regression

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.

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.