Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.
|
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Regression in Excel 2002 SP3 (Office XP)? | Excel Worksheet Functions | |||
Microsoft Excel in Microsoft works - how to open | Excel Discussion (Misc queries) | |||
linear interpolation function in excel | Excel Worksheet Functions | |||
Software that works well with excel to conduct monte carlo simula. | Excel Discussion (Misc queries) | |||
calculate non linear equation lines using excel | Excel Discussion (Misc queries) |