Regression Coefficients Incorrect with Centered predictors

[eplebel]

Recently, I have encountered some problems related to getting accurate regression coefficients in Excel 2003 (all Office updates installed) from a dataset with *small* (standard) numbers, which contains centered predictors. More specifically, I have a dataset with 18 observed data points containing a criterion , a centered predictor variable (x), another centered predictor variable (z), and the interaction of the two centered predictor variables (xz). This multiple regression equation is structured to test for interactions between the two continuous predictor variables (x and z) as prescribed by Aiken and West (1991) in their classic book.

When I run the regression in Excel with the centered predictors, some of the regression coefficents in the output are estimated to be 0, although they are clealry *not* 0 as estimated by SPSS 14.0.2. I have spent many hours troubleshooting this problem (and searched many forums on the internet) and still do not know why this is happening.

Initially, I thought the problem might have to do with the cross-product of the centered predictors, but even just doing a regression with one of the centered predictors (for certain centered predictors) yields a regression coefficient of 0 (although it should be non-zero as per SPSS 14.0.2). When doing these multiple regressions with non-centered predictors, all regression coefficients are estimated accurately.

I was wondering if anyone had any insights on why I am experiencing these problems.... If anyone wants a sample of some test data I have
used to troubleshoot these problems, you can download a file from: http://publish.uwo.ca/~elebel/test_222.xls

Thank you so much for your time.
Sincerely,
eplebel

p.s. It is interesting to note that I just read an article found at www.mis.coventry.ac.uk/~nhunt/pottel.pdf which stated that regressions
involving very *large* numbers may not yield accurate results due to problems with the STDEV function, but that centering the data before
doing the analysis will correct this problem in most cases.

 

[Legacy 14611]

Hi,

I had a bit of a look through your material.

Several comments.

Using your data I was unable to reproduce your excel regression results using your non-centered y, x, z and xz. I used the regression in the excel analysis toolpak (as you did) and also an entirely independent regression package. These both gave the same results - quite different from yours.

Your data look reasonably well balanced (or conditioned) and should cause no problems for excel's regression. The article you quoted is OK if taken in proper context (excel is not primarily a statistical package, but has limited handy statistical facilities, which must be used correctly, as must those from any other statistical package.) I think you may have been mislead by that discussion on centered vs non-centered variables.

Regressions with random data will not give coefficients equal to zero (or will do so with zero probability if you like to look at it that way). On the other hand it is possible to jack up the data or the regression so that they will give zero coefficients, and I suspect you have inadvertently done this, probably by using excel and spss to do somewhat different things.

I think the approach you are taking on interactions was first suggested by John Tukey quite a few years back (maybe 50 so so). I did read about it in C R Rao's book on Linear Statistical Inference, but have generally forgotten the details and don't have another reference handy. From memory, you can go a certain way with regression on that approach, but it has some limitations.

With your centered variable approach, x and z are OK (I'm not sure what you did about y), but are you sure you should have used the product of the centered x and z rather than the centered product of x and z? Is this really what Aiken and West (1991) recommended?

 

[eplebel]

problem "solved"... kind of

A person another forum seems to have the best explanation for the kind of problems i'm experiencing. see http://www.excelforum.com/showthread.php?p=1787188#post1787188

basically, EXCEL 2003 made some changes to the algorithms in LINEST and seems to arbitrarily set to 0 and regression coefficient it believes should be 0 (although the exact nature of this procedure and why it's happening isn't known).