I went back through about 40 past calibration data sets and quickly realized
the old curve fit data, which was calculated and stored using Excel 2000, was
now being corrupted using Excel 2003, (with all its patches), (about 50% of
the data curve fits were wrong). The quick solution, which will keep our
customers happy, is to open all of the data files in Excel 2000, save them,
and write a pdf backup file so this can not happen in the future. Thanks for
the help and a big raspberry to Microsoft for taking a perfectly good math
function and making it so it does not work any more on my data sets.
--
Jim
National Research Council of Canada
"Jerry W. Lewis" wrote:
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