The LINEST function returns an incorrect value in Excel 2003

  • Thread starter Thread starter Warren
  • Start date Start date
W

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?
 
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 said:
Did you try Office update to see if there are any updates for your Office?
Click to expand...

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!
 
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
 
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
 
Back
Top