The LINEST function returns an incorrect value in Excel 2003

[Warren]

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

suggests a fix for the bad coefficients that the LINEST routine returns when
working with large values. My problem is that the fix does not seem to work.
I have Excel 2003 with SP3 which is suppose to contain the fix. I have added
the appropriate registry value as outlined by the article to enable the fix.
I start Excel and open an example file containing the bad Linest rounding
behavior and see no difference as a result of the change.

Has anyone else gotten this to work properly?

 

[Jan Karel Pieterse]

Hi Warren,

I start Excel and open an example file containing the bad Linest rounding
behavior and see no difference as a result of the change.

And you're positive Excel is in automatic calculation mode?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Warren]

Hi Jan,

Yes, I thought of this too and checked it and it is in automatic calculation
mode (but just to be sure I pressed F9 a couple of times anyway).

 

[Jan Karel Pieterse]

Hi Warren,

Yes, I thought of this too and checked it and it is in automatic calculation
mode (but just to be sure I pressed F9 a couple of times anyway).

OK.

Well, I don't know why your LINEST still is wrong.

One thing I've learnt back when I got my classes in computer algorithms and
statistics is if you do any least-squares calculations it is wise to normalise
your data first. I mean this:
suppose your data is like:

x
100.1
100.2
100.3
100.4

(y values don't matter for this example)
Then if you want to do accurate regression, transform the x-es to:

x_transformed
0.1
0.2
0.3
0.4

and use those for the regression.
Sorry I can't solve your problem. Did you try Office update to see if there are
any updates for your Office?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Warren]

Did you try Office update to see if there are any updates for your Office?

Yes, I have tried office update and there is nothing new there to be
installed.

And while normalization may provide a perfectly reasonable workaround, my
real goal here is to eliminate the bug in Excel so that one less workaround
is necessary.

Thanks for taking the time to reply!

 

[Jan Karel Pieterse]

Hi Warren,

And while normalization may provide a perfectly reasonable workaround, my
real goal here is to eliminate the bug in Excel so that one less workaround
is necessary.

I sure understand that!

Thanks for taking the time to reply!

You're welcome. Sorry I couldn't be of more help.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Jerry W. Lewis]

Can you be more specific about your data and what you expected vs. what you
got from LINEST? That knowledge base article is decidedly vague about what
problem the hotfix is supposed to correct. The knowledge base article, does
not sound like the hotfix is intended to address the most glaring problem
with 2003 LINEST, namely that coeffiecient estimates of exactly zero are not
to be believed (probably a botched singularity check). In my experience,
that problem arises when two or more columns are essentially orthogonal and
have essentially the same dot product. Under those circumstances forming the
matrix equation
(X'X)^(-1)*X'y
(which was used by earlier versions and is easily implemented with Excel's
matrix functions MMULT, MINVERSE, and TRANSPOSE) does well numerically.

Jerry

 

[Jerry W. Lewis]

KB887964 resolves all of the bugs with LINEST 2003 that I am aware of. Can
you provide an example of your problem?

Note that simply installing the patch referenced in KB887964 is not
sufficient to activate the LINEST patch; you still have to manually make the
registry entry that is described in the KB article. If the following formula
returns zero in the first cell then you have not activated the LINEST patch
=LINEST({0.3;0;0.3;0.3},{2.3,0.1;0.1,-2.3;-0.1,2.3;-2.3,-0.1})
(since you are only concerned with the first output cell, it is sufficient
to enter the formula in a single cell, in which case array entry is not
required).

Note that neither large numbers nor a high number of significant figures
(the KB887964 Cause section is misleading) is required to demonstrate the
problem with unpatched LINEST 2003.

If you get 0.065094 in the first output cell of my example, yet get a wrong
answer for your problem, then either you have unrealistic expectations of
LINEST (a common problem) or you have discovered a bug with LINEST 2003 that
was previously unknown to me (less likely). Your mention of "large" numbers
raises the possibility that you are posing a problem that requires more than
IEEE double precision (used by Excel and most other numerical software) to
solve. If that is the case, then the transformation suggestion of Jan Karel
Pieterse may help.

Jerry

 

[Travis]

After modifying the registry, and reopening my excel file, I had to select the cell, then click in the formula bar (to the right of the "fx"), and then hit enter. Then that cell recalculated to the correct value.