Polynomial Formula Incorrect?

[DCLittlejohn]

Hello

I have a printed chart that shows a polynomial curve with temperature vaules
as X and resistivity values as Y. The X values are given in 500 degree
increments, and the Y in 0.1 increments. I recreated the printed chart using
the major gridline values from it and now have a trend chart in excel that
matches it.

Using the Polynomial trend function, excel has given me a matching curve and
formula of "y = 0.0014xE+4 - 0.0322xE+3 + 0.2571xE+2 - 0.7144x + 1.4873".

I have entered that formula to now solve for Y given different values of X.
All of the calculated values for Y are incorrect. For example - if X is 0, Y
should = 1. With the above formula Y = 1.4873. The values for Y get
progressively further away from what they should be the great the value for X.

[Mike Middleton]

DCLittlejohn -

Here are brief comments about three issues:

Excel Chart Type: Be sure to use an XY (Scatter) chart type, not a Line
chart type. The XY (Scatter) chart type will use the numerical X values; the
Line chart type uses 1,2,3,... for the X values.

Excel Precision: After fitting a trend function, select the equation text
box and repeatedly press the Increase Decimal button to display more
significant digits for the coefficients. Use the extra precision for
calculations. (You can also obtain the coefficients using the LINEST
worksheet function or by using VBA.)

Overfitting: Is there some physical reason that temperature and resistivity
should be related as a fourth-order polynomial? If not, it is likely you are
overfitting the data, and the fitted curve will not be useful for
predictions. This is a general statistical or data analysis issue, not an
Excel issue.

- Mike
www.mikemiddleton.com

"DCLittlejohn" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> I have a printed chart that shows a polynomial curve with temperature
> vaules
> as X and resistivity values as Y. The X values are given in 500 degree
> increments, and the Y in 0.1 increments. I recreated the printed chart
> using
> the major gridline values from it and now have a trend chart in excel that
> matches it.
>
> Using the Polynomial trend function, excel has given me a matching curve
> and
> formula of "y = 0.0014xE+4 - 0.0322xE+3 + 0.2571xE+2 - 0.7144x + 1.4873".
>
> I have entered that formula to now solve for Y given different values of
> X.
> All of the calculated values for Y are incorrect. For example - if X is 0,
> Y
> should = 1. With the above formula Y = 1.4873. The values for Y get
> progressively further away from what they should be the great the value
> for X.