Hi,

The attached file shows that Excel 2007 doesn't suffer from the bug for this particular data set.

Notice also that the xz coefficient is not zero and it does change as you move from 10 to 11 digits.

Humberto


At 05:59 AM 6/13/2007, Francois Sermier wrote:

Hello to all ASSUMErs,

Thanks to Neville Hunt and Etienne LeBel (in Canada), we discovered a very weird problem in Excel 2003, with the LINEST function. This is specially frustrating because XL2003 was publicized (including by me) as a version that fixed some calculation flaws in some statistical methods. H. Pottel pointed them out since a VERY long time.

Well, as it goes, fixing something somewhere always knocks out something else somewhere else. The improvement in the regression algorithm comes along with a WEIRD bug.

You may examine the attached file (in Excel 2003 only, for other versions, no problem (well, I hope so for XL2007...)). As noted by Etienne, some coefficients are zeroed in the LINEST function. Strange enough, the standard error of the same coefficient is not modified and all the statistics (R2, F, ...) are not affected.

Etienne thought it was an “improvement” zeroing unsignificant coefficient but the Students were not small. I thought about multicollinearity between the regressors. The truth is far worst !

1.      Read this (in the MSKB : Microsoft Knowledge base)

http://support.microsoft.com/kb/887964/en-us

“This problem occurs if there are more than nine significant digits in the source range values used by the LINEST function”.

(in my experiences it’s not always 9 digits, in the file it arises with 11 digits)

2.      Test that in the attached file... (modified from Etienne’s file)

3.      Fix it with reading the rest of the above technical note

Basically, to fix it, you have to add a key in the registry.
You may also use the .reg attached file
(caution: it’s a registry key, you have to merged it in the registry base)
(no responsibility..., you need it only if you have Excel 2003)
WELL no registry key attached : the .reg file is rejected by both recipients...

or using an “hotfix” dated 7 november 2005

you may find its name there:

http://support.microsoft.com/kb/903240/en-us

and download it somewhere else

If you read all this last TN, you will be horrified by all that can (or has ?) happen(ed) to your workbooks...


François Sermier
Paris, France

on 23/05/07 20:57, « Neville Hunt » <[log in to unmask]> forwarded the following :

Hi

...

 

Given that you may be associated with this work, I was wondering if you had any insight with a recent Excel issue that I have encountered. In the article, it is mentioned 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. However, recently I have encountered some problems related to getting accurate regression coefficients 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 (y), 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 you had any insights on why I am experiencing these problems.... If you want 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,

Etienne LeBel.


*********************************

 Etienne P. LeBel

 Department of Psychology

 Social Science Centre, RM 7305

 The University of Western Ontario

 London, Ontario, N6A 5C2

 

http://publish.uwo.ca/~elebel


 

 

NOTICE


This message and any files transmitted with it is intended for the addressee only and may contain information that is confidential or privileged. Unauthorised use is strictly prohibited. If you are not the addressee, you should not read, copy, disclose or otherwise use this message, except for the purpose of delivery to the addressee.


Any views or opinions expressed within this e-mail are those of the author and do not necessarily represent those of Coventry University.

Attachment converted: howlandf:TEST222B 1.XLS (XLS4/XCEL) (000CD742)

Humberto Barreto
x6315

--