Excel 2007 : Chart Linear Fit gone wacko

[ChrisB2011]

Hi all,

I have input a series of data into a table and formatted a scatter plot. When I fitted a 6th degree polynomial linear fit, the fit looks fine. However, when I use the equations it gives to calculate values in between the data points I get absurd numbers.

122 104 86 68
90 0.812 0.776 0.741 0.708
91 0.805 0.77 0.735 0.699
92 0.799 0.762 0.727 0.69
93 0.79 0.755 0.72 0.681
94 0.783 0.747 0.71 0.675
95 0.774 0.739 0.702 0.668
96 0.76 0.727 0.693 0.66
97 0.741 0.71 0.68 0.648
98 0.712 0.686 0.658 0.63
99 0.668 0.645 0.62 0.597
100 0.597 0.58 0.56 0.542


That is my data table. The 90-100 represents the x-axis while the decimal numbers represent the y-axis and the 122 104 86 and 68 are various tempurature categories. I'm not entirely sure how to get around this issue.

What I'm trying to do is basically automate the data table to give me feedback on any x value that I input.

Thanks in advace,
Chris

[shg]

You're more likely to get useful help if you post a workbook.

[MrShorty]

A workbook would help give specific advice. In general common mistakes I see:

1) If you are generating the trendline in a chart, by default the trendline shows limited digits for the coefficients. "Rounded" coefficients (especially for a high order polynomial) can give absurd results. Make sure the format the trendline to show all 15 digits. Or, my preference would be to generate the coefficients in the spreadsheet using the LINEST function.
2) If you neglect to specify the x values or if the x values contain numbers inadvertently stored as text, Excel will often choose the {1,2,3,4,5,6,....} array as the x-values in the trendline/regression. If you then use 90-100 as your x-values, you will of course get garbage because you used a different set of x values. Make sure all x values are actually numbers and not text or something Excel is interpreting as text.
3) High order polynomials are "flexible." Meaning that they can sometimes do wild things in between data points. It doesn't seem to me that a 6th order polynomial should be required for each data set. If you suspect this is a problem, try a lower order polynomial or, if warranted, maybe something completely different such as a function built on logarithms or powers.

[ChrisB2011]

Alright, here's the workbook. It doesn't look good yet, but its still in process.

Separator sizing samson.xlsm

[shg]

Please Login or Register  to view this content.

G33:K43 contains your data.

M31:Q31 contains the single array formula =LINEST(H34:H43,G34:G43^({1,2,3,4})). That returns the coefficients of a 4th-order fit for the first series.

M34 and copied down is the reconstruction formula: =SERIESSUM(G34, 4, -1, $M$31:$P$31) + $Q$31

[ChrisB2011]

That worked great. Thanks!