In article ,=20
says...
{snip}
=20
If the predictors exhibit near collinearity, the solution is to remove=20
predictors from the group of predictors that are nearly collinear. Is t=
here=20
an easy way to identify which predictors form a group that is nearly=20
collinear?
=20
To the extent that you can trust LINEST's algorithm, it tells you a=20
predictor is redundant by setting both the coefficient and the standard=20
error to zero.

--=20
Regards,

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

In article ,=20
says...
My reply is at the bottom.
=20
"Jerry W. Lewis" wrote:
=20
LINEST also returns #REF! when you exceed its hardcoded limit on=20
predictors. Empirically, that limit appears to be 17 predictors if no=
=20
constant is fitted, or 16 predictors and a fitted constant.
=20
As a practical matter I would be very suspicious of the numerical=20
properties of LINEST solutions long before hitting that hard coded=20
limit. Perhaps that is why it is there, even though MINVERSE in theory=
=20
can invert a 52x52 X'X matrix.
=20
Jerry
=20
Harlan Grove wrote:
=20
Peter N wrote...
=20
I am using Excel 2002. It seems to be unable to handle any more than

16
=20
predictor variables. All it returns is a #REF error. The help does

not
=20
document any limit. Is this an undocumented limitation? Is Excel

2003
=20
LINEST capable of handling more that 16 variables? How many?

=20
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.
=20
=20
My *EXACT* formula is =3DLINEST(Y14:Y50,A14:Q50,1,1)
=20
Is this hardcoded limit documented anywhere? Does Excel 2003 handle a=20
larger number of channels?
=20
I have a limited understanding of matrix math, but I understand that the=
=20
"Normal Equations" involves inverting the [X'X] matrix. To minimize roun=
doff=20
errors in the computation, each vector in the X matrix should first be=20
shifted (centered about its mean). Hopefully LINEST performs this operat=
ion=20
automatically and transparently.
=20
I read the article http://support.microsoft.com/kb/828533#kb3 and Microso=
ft=20
acknowledges weakness in numerical methods with LINEST in Excel 2002 and=
=20
earlier. This article claims that LINEST has been improved in Excel 2003=
, =20
using QR decomposition (I'll have to read up to understand that) resultin=
g in=20
more robust performance. They also advertise=20
=E2=A4=A2 Better numeric stability (generally smaller round off errors)=
=20
=E2=A4=A2 Analysis of collinearity issues=20
=20
In what form is the result of this "Analysis of collinearity" presented t=
o=20
the Excel user? What statistic in the matrix returned by LINEST should I =
look=20
at to know that the X matrix is "nearing" collinearity. =20
=20
If the predictors exhibit near collinearity, the solution is to remove=20
predictors from the group of predictors that are nearly collinear. Is t=
here=20
an easy way to identify which predictors form a group that is nearly=20
collinear?
=20