Several examples have been published in these newsgroups were LINEST in Excel
2003 can incorrectly return zero for one or more parameters. These issues
appear to have been fixed in Excel 2007 beta.
There is a post SP-2 hotfix that is supposed to correct some problem with
LINEST
http://support.microsoft.com/kb/887964
but the description of that problem is very obscurely written if it is
intended to fix this issue.
All of the zero parameter estimate problems I have seen up until now dealt
with a well conditioned but essentially orthogonal x matrix where columns of
the x-matrix had the same norm. Your example has a very ill-conditioned x
matrix where the zeroed parameter is very small relative to the other
parameters. In both cases, my best guess is that it is an unfortunate
excessive helpfulness along the lines of =A1-A2 returning zero if A1 and A2
are not exactly equal, but are equal when rounded to 15 decimal figures, as
was introduced in Excel 97
http://support.microsoft.com/kb/78113
Jerry
"JimK" wrote:
The Linest Function has severe errors when curve fitting my data as follows:
X Y
2499.89 -0.240420
4499.83 -0.432740
6499.77 -0.625070
8999.69 -0.865580
11499.83 -1.106090
13499.91 -1.298610
15999.74 -1.539220
18499.93 -1.779800
20500.05 -1.972495
21999.93 -2.116850
2499.89 -0.240400
4499.83 -0.432740
6499.77 -0.625070
8999.69 -0.865535
11499.83 -1.106125
13499.91 -1.298610
15999.74 -1.539235
18499.93 -1.779850
20500.05 -1.972410
21999.93 -2.116830
2499.89 -0.240430
4499.83 -0.432770
6499.77 -0.625150
8999.69 -0.865625
11499.83 -1.106175
13499.91 -1.298680
15999.74 -1.539240
18499.93 -1.779895
20500.05 -1.972440
21999.93 -2.116840
D C B
A
4.384643E-17 -4.849642E-12 -9.613158E-05 -7.173873E-05
If you change the first 'Y' value to -0.240419 (the real value), the Linest
Equation blows up and is wrong, (Value of 'D' is zero). The results are
below:
D C B A
0 -4.83784E-12 -9.61317E-05 8.03804E-05
Is there any fix by Microsoft in the works or is there something I can do to
ensure data sent to the customer is correct?
--
Jim
National Research Council of Canada