How do I conduct linear regression in Excel with more than 16 x's
 

The regresssion tool says the limit is 16 x's. I have 30.

One approach is run the regression twice, once with the even numbered points
and then with the odd numbered points. If there are no aliasing problems the
coefficients should match very closely.
--
Gary''s Student


"Ken" wrote:

The regresssion tool says the limit is 16 x's. I have 30.

You may need to buy SPSS!

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ken" wrote in message
...
The regresssion tool says the limit is 16 x's. I have 30.

Thanks for the suggestions. I see from an old thread that I missed (LINEST
maximum number of predictor variables 2/15/2005 3:35 PM PST ) that the 16
limit in XL is more than a hard-code limit; the method of calculation
apparently breaks down much before the limit of 16 is reached. Since I have
MATLAB, I will just export my data and compute the regression there. Thanks
to all for the help.

"Bernard Liengme" wrote:

You may need to buy SPSS!

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ken" wrote in message
...
The regresssion tool says the limit is 16 x's. I have 30.

Bernard Liengme wrote...
You may need to buy SPSS!

Why *buy* anything? R is very good and, with the contributed add-on
packages, fairly broad. Gretl, also free, would also be adequate for
this. I can understand why some companies may want to shell out real
$$$ for server or mainframe-based stats packages, but I can't
understand why individuals would spend close to or more than USD1000
for stats software.

The normal equations for least squares are
=MMULT(MINVERSE(MMULT(TRANSPOSE(xMat),xMat))),MMUL T(TRANSPOSE(xMat),yVec))
Which will work for up to 52 predictors if your X matrix is orthogonal.

This "method of calculation ... breaks down" for non-orthogonal X
matrices in the sense that the the useful information often ends up well
beyond the accuracy of IEEE double precision. A numerically better
approach is to do a singular value decomposition of the X matrix, so
that much of the numerical junk in (X'X)^-1 X'y can be canceled
analytically instead of needing far more precision than is available.

My vague recollection is that MATLAB also forms the normal equations,
and hence will have the same numerical instability as the excel formula
above. R (which Harlan suggested) is free
http://www.r-project.org/
and uses a more stable algorithm akin to the approach that I outlined above.

Jerry

Ken wrote:

Thanks for the suggestions. I see from an old thread that I missed (LINEST
maximum number of predictor variables 2/15/2005 3:35 PM PST ) that the 16
limit in XL is more than a hard-code limit; the method of calculation
apparently breaks down much before the limit of 16 is reached. Since I have
MATLAB, I will just export my data and compute the regression there. Thanks
to all for the help.

"Bernard Liengme" wrote:


You may need to buy SPSS!

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ken" wrote in message
...

The regresssion tool says the limit is 16 x's. I have 30.