Problems with LINEST in Excel 2003

[hypersonic]

My problem is the use of LINEST in Excel 2003, to which we have recentl
upgraded.

Previously we used Excel 97 and I was able to produce realistic result
from this function.

I plot a set of data and create a 4th order polynomial trend line. Th
chart formula for the trend line is the same in both Excel 2003 an
97.

When i use the functions:

=INDEX(LINEST(range_y; range_x^{1,2,3,4};1)
=INDEX(LINEST(range_y; range_x^{1,2,3,4};2)
=INDEX(LINEST(range_y; range_x^{1,2,3,4};3)
=INDEX(LINEST(range_y; range_x^{1,2,3,4};4)
=INDEX(LINEST(range_y; range_x^{1,2,3,4};5)

in 2003 a get different coefs from those shown in the equation on th
graph. This did not happen in 97.

Then if I plot the polynomial from the LINEST generated coefs i get
line that is somewhat similar to the trend but no way near as good a
97.

The Chart in linest prob.xls illustrates this problem (in attached zi
file)

The chart generated coefs in 2003 are exactly the same as the LINES
generated coefs in 97.

I need to generate 216 polynomial coef sets for varying data sets s
copying and pasting from the graphs is not an option!

I have read in forums that the new decomposition method used for LINES
in Excel 2003 is supposed to be an improvement but this does not sea
valid in my case.

I would be very grateful of any advice you could give me concerning m
problem.

I have attached the excel spread sheet (linest prob.xls)

thanks in advance for your help

Sam Zakrzewski
Haldor Topsoe A/

Attachment filename: linest prob.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=65113

 

[hgrove]

hypersonic wrote...

My problem is the use of LINEST in Excel 2003, to which we have
recently upgraded.


Previously we used Excel 97 and I was able to produce realistic
results from this function.

I plot a set of data and create a 4th order polynomial trend line.
The chart formula for the trend line is the same in both Excel
2003 and 97.

When i use the functions:

=INDEX(LINEST(range_y; range_x^{1,2,3,4};1)
=INDEX(LINEST(range_y; range_x^{1,2,3,4};2)
=INDEX(LINEST(range_y; range_x^{1,2,3,4};3)
=INDEX(LINEST(range_y; range_x^{1,2,3,4};4)
=INDEX(LINEST(range_y; range_x^{1,2,3,4};5)

in 2003 a get different coefs from those shown in the equation
on the graph. This did not happen in 97. ...
I have read in forums that the new decomposition method used >fo

LINEST in Excel 2003 is supposed to be an improvement but >this doe
not seam valid in my case.
...

I have attached the excel spread sheet (linest prob.xls)

General advice: no one in their right mind opens workbooks fro
strangers. The risks far outweigh the benefits. That there have bee
some people who have downloaded your attachment do far only confirm
that there are people not in their right minds who read newsgroup
using excelforum. The people who know how LINEST works and would b
likely to help are almost certainly not going to look at your file.

Even if your datasets were large, paste a problem dataset AS PLAIN TEX
into a follow-up. The people who are likely to respond would prefe
that, and they'll be able to figure out how to write the formulas an
graph the results themselves.

That said, if any value in the X range doesn't contain a number, XL97'
LINEST and chart trendlines ignore *ALL* the X range values an uses th
default sequence 1, 2, 3, . . ., N, where N is the number of entries i
Y. It's not unlikely XL2003's chart trendlines do the same thing, but
suspect its LINEST doesn't do that any more (there have been reporte
differences in SLOPE and INTERCEPT between XL2003 and earlier version
due to the handling of nonnumeric entries in X). So make sure *ALL
your X values are numbers, not text and not blanks.

If it transpires that you have had some nonnumbers in your X values
then XL2003's LINEST is finally giving you a correct result