LINEST maximum number of predictor variables

[Guest]

I am using Excel 2002. It seems to be unable to handle any more than 16
predictor variables. All it returns is a #REF error. The help does not
document any limit. Is this an undocumented limitation? Is Excel 2003
LINEST capable of handling more that 16 variables? How many?

 

[Harlan Grove]

Peter N wrote...

I am using Excel 2002. It seems to be unable to handle any more than 16
predictor variables. All it returns is a #REF error. The help does not
document any limit. Is this an undocumented limitation? Is Excel 2003
LINEST capable of handling more that 16 variables? How many?

What's your *EXACT* formula? LINEST should return #VALUE! and #NUM!
errors when it can't invert the bilinear form of the independent
variables, but it only returns #REF! when there's a true range
reference error.

 

[Jerry W. Lewis]

LINEST also returns #REF! when you exceed its hardcoded limit on
predictors. Empirically, that limit appears to be 17 predictors if no
constant is fitted, or 16 predictors and a fitted constant.

As a practical matter I would be very suspicious of the numerical
properties of LINEST solutions long before hitting that hard coded
limit. Perhaps that is why it is there, even though MINVERSE in theory
can invert a 52x52 X'X matrix.

Jerry

 

[Guest]

My reply is at the bottom.

LINEST also returns #REF! when you exceed its hardcoded limit on
predictors. Empirically, that limit appears to be 17 predictors if no
constant is fitted, or 16 predictors and a fitted constant.

As a practical matter I would be very suspicious of the numerical
properties of LINEST solutions long before hitting that hard coded
limit. Perhaps that is why it is there, even though MINVERSE in theory
can invert a 52x52 X'X matrix.

Jerry

My *EXACT* formula is =LINEST(Y14:Y50,A14:Q50,1,1)

Is this hardcoded limit documented anywhere? Does Excel 2003 handle a
larger number of channels?

I have a limited understanding of matrix math, but I understand that the
"Normal Equations" involves inverting the [X'X] matrix. To minimize roundoff
errors in the computation, each vector in the X matrix should first be
shifted (centered about its mean). Hopefully LINEST performs this operation
automatically and transparently.

I read the article http://support.microsoft.com/kb/828533#kb3 and Microsoft
acknowledges weakness in numerical methods with LINEST in Excel 2002 and
earlier. This article claims that LINEST has been improved in Excel 2003,
using QR decomposition (I'll have to read up to understand that) resulting in
more robust performance. They also advertise
• Better numeric stability (generally smaller round off errors)
• Analysis of collinearity issues

In what form is the result of this "Analysis of collinearity" presented to
the Excel user? What statistic in the matrix returned by LINEST should I look
at to know that the X matrix is "nearing" collinearity.

If the predictors exhibit near collinearity, the solution is to remove
predictors from the group of predictors that are nearly collinear. Is there
an easy way to identify which predictors form a group that is nearly
collinear?

 

[Tushar Mehta]

{snip}

If the predictors exhibit near collinearity, the solution is to remove
predictors from the group of predictors that are nearly collinear. Is there
an easy way to identify which predictors form a group that is nearly
collinear?

To the extent that you can trust LINEST's algorithm, it tells you a
predictor is redundant by setting both the coefficient and the standard
error to zero.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

My reply is at the bottom.

LINEST also returns #REF! when you exceed its hardcoded limit on
predictors. Empirically, that limit appears to be 17 predictors if no
constant is fitted, or 16 predictors and a fitted constant.

As a practical matter I would be very suspicious of the numerical
properties of LINEST solutions long before hitting that hard coded
limit. Perhaps that is why it is there, even though MINVERSE in theory
can invert a 52x52 X'X matrix.

Jerry

My *EXACT* formula is =LINEST(Y14:Y50,A14:Q50,1,1)

Is this hardcoded limit documented anywhere? Does Excel 2003 handle a
larger number of channels?

I have a limited understanding of matrix math, but I understand that the
"Normal Equations" involves inverting the [X'X] matrix. To minimize roundoff
errors in the computation, each vector in the X matrix should first be
shifted (centered about its mean). Hopefully LINEST performs this operation
automatically and transparently.

I read the article http://support.microsoft.com/kb/828533#kb3 and Microsoft
acknowledges weakness in numerical methods with LINEST in Excel 2002 and
earlier. This article claims that LINEST has been improved in Excel 2003,
using QR decomposition (I'll have to read up to understand that) resulting in
more robust performance. They also advertise
⤢ Better numeric stability (generally smaller round off errors)
⤢ Analysis of collinearity issues

In what form is the result of this "Analysis of collinearity" presented to
the Excel user? What statistic in the matrix returned by LINEST should I look
at to know that the X matrix is "nearing" collinearity.

If the predictors exhibit near collinearity, the solution is to remove
predictors from the group of predictors that are nearly collinear. Is there
an easy way to identify which predictors form a group that is nearly
collinear?

 

[Michael R Middleton]

Peter N -

Is there an easy way to identify which predictors form a group that is
nearly collinear? <

Before looking for group collinearity, a primitive first step is to use the
Correlation tool on all predictors to look for high values of r between
pairs of prospective predictors.

- Mike

www.mikemiddleton.com

 

[Jerry W. Lewis]

Peter N wrote:

....

:

....
Is this hardcoded limit documented anywhere? Does Excel 2003 handle a
larger number of channels?

I have not seen it documented, unless you count
http://groups-beta.google.com/group/microsoft.public.excel.programming/msg/03daea364dd8957c

Excel 2003 has the same limit.

I have a limited understanding of matrix math, but I understand that the
"Normal Equations" involves inverting the [X'X] matrix. To minimize roundoff
errors in the computation, each vector in the X matrix should first be
shifted (centered about its mean). Hopefully LINEST performs this operation
automatically and transparently.

I do not believe that 2003 centers vectors.

I read the article http://support.microsoft.com/kb/828533#kb3 and Microsoft
acknowledges weakness in numerical methods with LINEST in Excel 2002 and
earlier. This article claims that LINEST has been improved in Excel 2003,
using QR decomposition (I'll have to read up to understand that) resulting in
more robust performance. They also advertise
• Better numeric stability (generally smaller round off errors)
• Analysis of collinearity issues

QR or SV (singular value) decomposition of X is definitely the most
accurate way to go, but there are issues with the 2003 implementation as
well

http://groups-beta.google.com/group...read/thread/aaa78a91ec42fd4b/9457dccf7aa83b61

Jerry

 

[Martin Brown]

My reply is at the bottom.

It is probably falling over and giving wrong answers at around 6 or 7
variables unless your predictors are nearly orthogonal to begin with. On
some tricky cases the old Excel LINEST could fail when fitting anything
beyond a cubic polynomial.

My *EXACT* formula is =LINEST(Y14:Y50,A14:Q50,1,1)

Is this hardcoded limit documented anywhere? Does Excel 2003 handle a
larger number of channels?

I have a limited understanding of matrix math, but I understand that the
"Normal Equations" involves inverting the [X'X] matrix. To minimize roundoff
errors in the computation, each vector in the X matrix should first be
shifted (centered about its mean). Hopefully LINEST performs this operation
automatically and transparently.

It would be safer (and more nearly true) to assume that it doesn't work
unless you are very careful to precondition the problem so that naive
code can solve it.

I read the article http://support.microsoft.com/kb/828533#kb3 and Microsoft
acknowledges weakness in numerical methods with LINEST in Excel 2002 and
earlier. This article claims that LINEST has been improved in Excel 2003,
using QR decomposition (I'll have to read up to understand that) resulting in
more robust performance. They also advertise
• Better numeric stability (generally smaller round off errors)
• Analysis of collinearity issues

It is pretty scary that earlier versions did not use QR decomposition!

In what form is the result of this "Analysis of collinearity" presented to
the Excel user? What statistic in the matrix returned by LINEST should I look
at to know that the X matrix is "nearing" collinearity.

If the predictors exhibit near collinearity, the solution is to remove
predictors from the group of predictors that are nearly collinear. Is there
an easy way to identify which predictors form a group that is nearly
collinear?

Compute their dot product divided by their magnitude.
Eg vectors A1..An, B1..Bn

=SUMPRODUCT(A1..An,B1..Bn)/SQRT(SUMPRODUCT(A1..An,A1..An)*SUMPRODUCT(B1..Bn,B1..Bn))

This is zero if they are exactly orthogonal (good thing) or +/- 1 if
they are exactly collinear (very very bad). The closer the predictors
are to being collinear the more unstable the matrix problem becomes.

There are devious ways to fit orthogonal polynomials reliably in Excel
if you really need to do it. Allowing 16 general predictors to be used
without any defensive coding is very optimistic of them!

Fitting general vectors to data is frought with difficulties because of
the intrinsic numerical instability in the Excel algorithms. You must
always check that the fitted model reproduces your data to within the
claimed level of residual error. Trust nothing where LINEST is
concerned. The 2003 version is better but still not right.

Regards,
Martin Brown