LINEST

[Sven Krueger]

Hi,
does anybody know what is behind the LINEST-function, when using it for
a mutiple regression analysis ? The fornulars for a linear regression
analyses are shown in the help menue, but unfortunately not for the
mutiple case.

Thanx,
Sven

[Bernard V Liengme]

Hi Sven,
Read a numerical analysis book on the topic "least-squares fit" This should
help you understand the principle behind the method.
Bernard

[Jerry W. Lewis]

LINEST uses a matrix formula for multiple regression. A closed form
formula for a single term of the model would depend on how many terms
are in the model, and could be quite complicated. However, the matrix
formula is quite simple

b = (X'X)^-1 X'y

y is the (column) vector of observations
b is the (column) vector of model estimates
X is the model matrix -- rows correspond to the observations in y,
columns correspond to the model terms
' is the matrix transpose operator
^-1 is matrix inverse

For example, (in Excel 97) Help on LINEST gives a multiple regression
example with 4 regressors and eleven observations. Their formula is
=LINEST(E2:E12,A2:D12,TRUE,TRUE). If you insert a column before column
A, then this becomes
=LINEST(F2:F12,B2:E12,TRUE,TRUE).
Enter a one in each row of the new column A, then

=TRANSPOSE(MMULT(MINVERSE(MMULT(TRANSPOSE(A2:E12),A2:E12)),MMULT(TRANSPOSE(A2:E12),F2:F12)))
gives the same extimates as LINEST (but in reverse order)

Note: when I enter their data and use their formula, LINEST gets their
answer only approximately, so use LINEST as well as the matrix formula,
rather than trusting the "answer" in Help.

Cheers,
Jerry