Apr 28

How to use Excel’s LINEST() function to return multivariate regression - 10 min. screencast

by David Harper, CFA, FRM, CIPM

I like the LINEST() function for regression results because it's dynamic. You don't need to re-run it if the data changes. To illustrate, I am using Gujarati's dataset: a hypothetical antique clock auction. The dependent variable is the price paid for an antique clock, by the winning bidder. The independent or explanatory variables are the age of the clock (X2) and the number of bidders (X3):

LINEST() returns an array. Before entering, select a RANGE. And to finish the formula, don't finish with the ENTER key. Finish with CTRL+SHIFT+ENTER.

A few things about the output:

It is a three-variable regression (one dependent, Y, plus two independents). So, the d.f. are 29 (32 observations - 3) and the regression has four (not three) standard errors: there is a standard error for each variable plus a standard error for the slope coefficient.
The first two rows are telling:
The first row contains the coefficients. In this case, the line is: Y = -1336 + 12.7(X2) + 85.8(X3)
The second row contains corresponding standard errors (the standard deviations of the sample variation in the coefficients). We can divide the second row into the first row to obtain computed (student's) t values. For example, 85.764/8.802 = 9.7. That's far greater than 2.x, so this tells us the X3 coefficient (number of bidders) is statistically significant. In other words, its true value is probably not zero.