Trendline factor extraction for 6th order

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kmartin - 09 Oct 2007 16:40 GMT

I am attempting to extract polonomial factors for a data set. At 6th order,
the process falls apart.
My data is
X=0,100,200,300,400,500,600,700,800,900,100
Y=0,0.1,-3.1,-12,-29.5,59.2,-106.4,177.6,280.2,421.3,606.2

At 6th order the C6,b and R-aquared values are crazy.

My formula are:
C6=INDEX(LINEST(y,x^{1,2,3,4,5,6}),1)
b=INDEX(LINEST(y,x^{1,2,3,4,5,6}),1,7)
R-squared=INDEX(LINEST(y,x^{1,2,3,4,5,6},TRUE,TRUE),3)

Any suggestions?

In addition to this, I would like to determine if using these functions, can
the trendline factor calculation be forced thru the origin 0,0?

Reply to this Message

Martin Brown - 09 Oct 2007 18:44 GMT

> I am attempting to extract polonomial factors for a data set. At 6th order,
> the process falls apart.
[quoted text clipped - 3 lines]
>
> At 6th order the C6,b and R-aquared values are crazy.

The worksheet functions LINEST etc are only marginally stable for
difficult cubic polynomials. They are dead in the water at 6th order.
The polynomial fit in the chart of XL2002 and earlier is much better
numerically and can sometimes solve much harder problems accurately.
It has been discussed here previously. You need to display all
siginifcant digits in the equation to get sensible results recreating
the fitted line in a spreadsheet.

A quick eyeball of your data suggests that not even a 6th order
polynomial fit will make sense of that set of points. You really need
to fit a physical model to your data rather than the highest order
polynomial available.

> My formula are:
> C6=INDEX(LINEST(y,x^{1,2,3,4,5,6}),1)
[quoted text clipped - 5 lines]
> In addition to this, I would like to determine if using these functions, can
> the trendline factor calculation be forced thru the origin 0,0?

The chart algorithm can. Although it will make the fit worse.

Normalising the x values so that they are in the range [-1,1] will
improve the condition number of the problem and at least give LINEST a
sporting chance of solving it numerically.

That is define a new column X' = (X - 500)/500 and use that as the new
dependent variable.

Regards,
Martin Brown

Reply to this Message