6th order polynomial equ yields unexpected results

Hi,

If I evaluate my 6th order poly equation in Excell, it yields unexpected
results, but the eq works well for 1 to 5th order polynomial trend chart.
is there another way to work with 6th order poly eq. Any help would be
highly appreciated.

Thanks
Sanjay Limbikai

Check if you should even be using a 6th order polynomial.

See the 'Over-specifying a regression' section in
Trendline coefficients
http://www.tushar-mehta.com/excel/ti...efficients.htm

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <[email protected]>,
[email protected] says...
> Hi,
>
> If I evaluate my 6th order poly equation in Excell, it yields unexpected
> results, but the eq works well for 1 to 5th order polynomial trend chart.
> is there another way to work with 6th order poly eq. Any help would be
> highly appreciated.
>
> Thanks
> Sanjay Limbikai
>

You have given far too little information for us to be able to
accurately diagnose.

What version of Excel?
How are you fitting the polynomial? (chart trendline equation, LINEST,
other?)
What unexpected results are you getting?
Is the data set small enough that you could reasonably include it in
your post? (body text, no attachments, please)

While it is quite likely that you are over-fitting the data (as Tushar
suggested), it is not clear how that, in and of itself, would produce
"unexpected results". If you are using LINEST in versions prior to
Excel 2003, you could easily be in terretory where LINEST's algorithm
has numerical difficulties (how do coefficient estimates compare with
the chart trendline coefficients? [which is much better numerically]).
LINEST in Excel 2003 is much better numerically than previous versions,
but coefficients that are exactly zero are not to be trusted (again, how
do they compare to the chart trendline coefficients?). If you are using
the chart trendline coefficients and copying them into a worksheet for
further calculation, did you obtain them to full precision (format the
chart equation element to scientific notation with 14 decimal places) or
did you copy the heavily rounded values that Excel displays by default?

Jerry

Sanjay Kumar Limbikai wrote:

> Hi,
>
> If I evaluate my 6th order poly equation in Excell, it yields unexpected
> results, but the eq works well for 1 to 5th order polynomial trend chart.
> is there another way to work with 6th order poly eq. Any help would be
> highly appreciated.
>
> Thanks
> Sanjay Limbikai

Sorry for confusing you,

Here it is more-
Excell 2002,
I was comparing coefficients with extracting using LINEST function to the
constants of displayed trend eq (6th order) with full precission. They are
all different.

Thanks for your valuable information.
Sanjay Limbikai

"Jerry W. Lewis" wrote:

> You have given far too little information for us to be able to
> accurately diagnose.
>
> What version of Excel?
> How are you fitting the polynomial? (chart trendline equation, LINEST,
> other?)
> What unexpected results are you getting?
> Is the data set small enough that you could reasonably include it in
> your post? (body text, no attachments, please)
>
> While it is quite likely that you are over-fitting the data (as Tushar
> suggested), it is not clear how that, in and of itself, would produce
> "unexpected results". If you are using LINEST in versions prior to
> Excel 2003, you could easily be in terretory where LINEST's algorithm
> has numerical difficulties (how do coefficient estimates compare with
> the chart trendline coefficients? [which is much better numerically]).
> LINEST in Excel 2003 is much better numerically than previous versions,
> but coefficients that are exactly zero are not to be trusted (again, how
> do they compare to the chart trendline coefficients?). If you are using
> the chart trendline coefficients and copying them into a worksheet for
> further calculation, did you obtain them to full precision (format the
> chart equation element to scientific notation with 14 decimal places) or
> did you copy the heavily rounded values that Excel displays by default?
>
> Jerry
>
> Sanjay Kumar Limbikai wrote:
>
> > Hi,
> >
> > If I evaluate my 6th order poly equation in Excell, it yields unexpected
> > results, but the eq works well for 1 to 5th order polynomial trend chart.
> > is there another way to work with 6th order poly eq. Any help would be
> > highly appreciated.
> >
> > Thanks
> > Sanjay Limbikai
>
>

Two possibilities depending on the type of chart you used:

- "Line" chart: The chart is likely not doing what you intended. A
"Line" chart is misleadingly named and has nothing to do with whether
you want points joined by a line or not. A trendline on a "Line" chart
uses x-values of 1,2,3,... regardless of the x-values that you may have
specified. Consequently, the the polynomial trendline from a "Line"
chart is probably meaningless.

- "X-Y (Scatter)" chart: The x-values do not span a wide enough range
of values for LINEST to be able to estimate the coefficients accurately.
The algorithm used by the chart trendline is more robust. If you post
the data (text in body of post; no attachments, please) then I could
comment on the accuracy (or possible inaccuracy) of the chart coefficients.

Jerry

Sanjay Kumar Limbikai wrote:

> Sorry for confusing you,
>
> Here it is more-
> Excell 2002,
> I was comparing coefficients with extracting using LINEST function to the
> constants of displayed trend eq (6th order) with full precission. They are
> all different.
>
> Thanks for your valuable information.
> Sanjay Limbikai
>
> "Jerry W. Lewis" wrote:
>
>
>>You have given far too little information for us to be able to
>>accurately diagnose.
>>
>>What version of Excel?
>>How are you fitting the polynomial? (chart trendline equation, LINEST,
>>other?)
>>What unexpected results are you getting?
>>Is the data set small enough that you could reasonably include it in
>>your post? (body text, no attachments, please)
>>
>>While it is quite likely that you are over-fitting the data (as Tushar
>>suggested), it is not clear how that, in and of itself, would produce
>>"unexpected results". If you are using LINEST in versions prior to
>>Excel 2003, you could easily be in terretory where LINEST's algorithm
>>has numerical difficulties (how do coefficient estimates compare with
>>the chart trendline coefficients? [which is much better numerically]).
>>LINEST in Excel 2003 is much better numerically than previous versions,
>>but coefficients that are exactly zero are not to be trusted (again, how
>>do they compare to the chart trendline coefficients?). If you are using
>>the chart trendline coefficients and copying them into a worksheet for
>>further calculation, did you obtain them to full precision (format the
>>chart equation element to scientific notation with 14 decimal places) or
>>did you copy the heavily rounded values that Excel displays by default?
>>
>>Jerry
>>
>>Sanjay Kumar Limbikai wrote:
>>
>>
>>>Hi,
>>>
>>>If I evaluate my 6th order poly equation in Excell, it yields unexpected
>>>results, but the eq works well for 1 to 5th order polynomial trend chart.
>>>is there another way to work with 6th order poly eq. Any help would be
>>>highly appreciated.
>>>
>>>Thanks
>>>Sanjay Limbikai
>>>
>>

Hello Jerry,

Here is the data

Date Data Points
01/01/1976 3138
01/01/1976 3247
08/18/1977 3163
01/30/1978 3185
04/01/1978 3014
07/01/1978 3199
07/07/1978 3116
10/30/1978 3185
01/18/1979 3074

Though this is the first few records of my data,
I used to draw a chart of poly trend line of 6th order (Excell2002),
and used to extract constants - =LINEST(B2:B10,A2:A10^{1,2,3,4,5,6})
the coefficients are different as of trend line equ.

Sanjay



"Jerry W. Lewis" wrote:

> Two possibilities depending on the type of chart you used:
>
> - "Line" chart: The chart is likely not doing what you intended. A
> "Line" chart is misleadingly named and has nothing to do with whether
> you want points joined by a line or not. A trendline on a "Line" chart
> uses x-values of 1,2,3,... regardless of the x-values that you may have
> specified. Consequently, the the polynomial trendline from a "Line"
> chart is probably meaningless.
>
> - "X-Y (Scatter)" chart: The x-values do not span a wide enough range
> of values for LINEST to be able to estimate the coefficients accurately.
> The algorithm used by the chart trendline is more robust. If you post
> the data (text in body of post; no attachments, please) then I could
> comment on the accuracy (or possible inaccuracy) of the chart coefficients.
>
> Jerry
>
> Sanjay Kumar Limbikai wrote:
>
> > Sorry for confusing you,
> >
> > Here it is more-
> > Excell 2002,
> > I was comparing coefficients with extracting using LINEST function to the
> > constants of displayed trend eq (6th order) with full precission. They are
> > all different.
> >
> > Thanks for your valuable information.
> > Sanjay Limbikai
> >
> > "Jerry W. Lewis" wrote:
> >
> >
> >>You have given far too little information for us to be able to
> >>accurately diagnose.
> >>
> >>What version of Excel?
> >>How are you fitting the polynomial? (chart trendline equation, LINEST,
> >>other?)
> >>What unexpected results are you getting?
> >>Is the data set small enough that you could reasonably include it in
> >>your post? (body text, no attachments, please)
> >>
> >>While it is quite likely that you are over-fitting the data (as Tushar
> >>suggested), it is not clear how that, in and of itself, would produce
> >>"unexpected results". If you are using LINEST in versions prior to
> >>Excel 2003, you could easily be in terretory where LINEST's algorithm
> >>has numerical difficulties (how do coefficient estimates compare with
> >>the chart trendline coefficients? [which is much better numerically]).
> >>LINEST in Excel 2003 is much better numerically than previous versions,
> >>but coefficients that are exactly zero are not to be trusted (again, how
> >>do they compare to the chart trendline coefficients?). If you are using
> >>the chart trendline coefficients and copying them into a worksheet for
> >>further calculation, did you obtain them to full precision (format the
> >>chart equation element to scientific notation with 14 decimal places) or
> >>did you copy the heavily rounded values that Excel displays by default?
> >>
> >>Jerry
> >>
> >>Sanjay Kumar Limbikai wrote:
> >>
> >>
> >>>Hi,
> >>>
> >>>If I evaluate my 6th order poly equation in Excell, it yields unexpected
> >>>results, but the eq works well for 1 to 5th order polynomial trend chart.
> >>>is there another way to work with 6th order poly eq. Any help would be
> >>>highly appreciated.
> >>>
> >>>Thanks
> >>>Sanjay Limbikai
> >>>
> >>
>
>

Sanjay Kumar Limbikai wrote:

> Sorry for confusing you,
>
> Here it is more-
> Excell 2002,
> I was comparing coefficients with extracting using LINEST function to the
> constants of displayed trend eq (6th order) with full precission. They are
> all different.

Almost certainly the solution shown by the graphical chart polynomial is
a better fit than the one from LINEST. The simplest way to test this is
to compute the polynomial fit out for all your datapoints and then
explicitly sum up the squares of the residuals in a spreadsheet model.

Whichever one has more nearly minimised the sum of the squares is best.

Prior to Excel 2003 LINEST is potentially suspect on anything more
complex than a cubic polynomial for some nasty datasets. You might be
able to get better agreement between the two fits by using days since
1/1/1976 as the x variable (dates have a huge additive constant in
them). It should help improve the numerical stability of the problem a bit.

Regards,
Martin Brown

This Least Squares problem is ridiculously difficult numerically.
Dedicated statistics packages will refuse to do it at all, unless you
first orthogonalize the predictors yourself.

Excel dates are stored as the number of days since 1900, so the numeric
values in column A are

27760, 27760, 28355, 28520, 28581, 28672, 28678, 28793, 28873

which has a coefficient of variation (relative standard deviation)
<1.5%. Consequently the condition number of X'X for solving the normal
equations is <3E+80, which means that Excel would potentially need
between 5 and 6 times its actual precision for LINEST to reliably
recognize that X'X is invertable.

Correct coefficients are (to 15 digits; obtained algebraically in Maple)

1.13729790060870E+15 (intercept)
-2.39465281115860E+11 (x)
2.10079765828648E+07 (x^2)
-9.82899962202329E+02 (x^3)
2.58667425348765E-02 (x^4)
-3.63042771974839E-07 (x^5)
2.12298835919803E-12 (x^6)

From this, you can see that chart coefficients are accurate to around
4 figures on this problem (which indicates just how good the chart
algorithm is). LINEST in Excel 2003 gives about 3 figure accuracy.

Jerry

Sanjay Kumar Limbikai wrote:

> Hello Jerry,
>
> Here is the data
>
> Date Data Points
> 01/01/1976 3138
> 01/01/1976 3247
> 08/18/1977 3163
> 01/30/1978 3185
> 04/01/1978 3014
> 07/01/1978 3199
> 07/07/1978 3116
> 10/30/1978 3185
> 01/18/1979 3074
>
> Though this is the first few records of my data,
> I used to draw a chart of poly trend line of 6th order (Excell2002),
> and used to extract constants - =LINEST(B2:B10,A2:A10^{1,2,3,4,5,6})
> the coefficients are different as of trend line equ.
>
> Sanjay

If you plot the data you will quickly see that the only trend is a flat
horizontal line or, with the application of a fair dose of imagination,
a slight downward sloping trend.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> Hello Jerry,
>
> Here is the data
>
> Date Data Points
> 01/01/1976 3138
> 01/01/1976 3247
> 08/18/1977 3163
> 01/30/1978 3185
> 04/01/1978 3014
> 07/01/1978 3199
> 07/07/1978 3116
> 10/30/1978 3185
> 01/18/1979 3074
>
> Though this is the first few records of my data,
> I used to draw a chart of poly trend line of 6th order (Excell2002),
> and used to extract constants - =LINEST(B2:B10,A2:A10^{1,2,3,4,5,6})
> the coefficients are different as of trend line equ.
>
> Sanjay
>
>
>
> "Jerry W. Lewis" wrote:
>
> > Two possibilities depending on the type of chart you used:
> >
> > - "Line" chart: The chart is likely not doing what you intended. A
> > "Line" chart is misleadingly named and has nothing to do with whether
> > you want points joined by a line or not. A trendline on a "Line" chart
> > uses x-values of 1,2,3,... regardless of the x-values that you may have
> > specified. Consequently, the the polynomial trendline from a "Line"
> > chart is probably meaningless.
> >
> > - "X-Y (Scatter)" chart: The x-values do not span a wide enough range
> > of values for LINEST to be able to estimate the coefficients accurately.
> > The algorithm used by the chart trendline is more robust. If you post
> > the data (text in body of post; no attachments, please) then I could
> > comment on the accuracy (or possible inaccuracy) of the chart coefficients.
> >
> > Jerry
> >
> > Sanjay Kumar Limbikai wrote:
> >
> > > Sorry for confusing you,
> > >
> > > Here it is more-
> > > Excell 2002,
> > > I was comparing coefficients with extracting using LINEST function to the
> > > constants of displayed trend eq (6th order) with full precission. They are
> > > all different.
> > >
> > > Thanks for your valuable information.
> > > Sanjay Limbikai
> > >
> > > "Jerry W. Lewis" wrote:
> > >
> > >
> > >>You have given far too little information for us to be able to
> > >>accurately diagnose.
> > >>
> > >>What version of Excel?
> > >>How are you fitting the polynomial? (chart trendline equation, LINEST,
> > >>other?)
> > >>What unexpected results are you getting?
> > >>Is the data set small enough that you could reasonably include it in
> > >>your post? (body text, no attachments, please)
> > >>
> > >>While it is quite likely that you are over-fitting the data (as Tushar
> > >>suggested), it is not clear how that, in and of itself, would produce
> > >>"unexpected results". If you are using LINEST in versions prior to
> > >>Excel 2003, you could easily be in terretory where LINEST's algorithm
> > >>has numerical difficulties (how do coefficient estimates compare with
> > >>the chart trendline coefficients? [which is much better numerically]).
> > >>LINEST in Excel 2003 is much better numerically than previous versions,
> > >>but coefficients that are exactly zero are not to be trusted (again, how
> > >>do they compare to the chart trendline coefficients?). If you are using
> > >>the chart trendline coefficients and copying them into a worksheet for
> > >>further calculation, did you obtain them to full precision (format the
> > >>chart equation element to scientific notation with 14 decimal places) or
> > >>did you copy the heavily rounded values that Excel displays by default?
> > >>
> > >>Jerry
> > >>
> > >>Sanjay Kumar Limbikai wrote:
> > >>
> > >>
> > >>>Hi,
> > >>>
> > >>>If I evaluate my 6th order poly equation in Excell, it yields unexpected
> > >>>results, but the eq works well for 1 to 5th order polynomial trend chart.
> > >>>is there another way to work with 6th order poly eq. Any help would be
> > >>>highly appreciated.
> > >>>
> > >>>Thanks
> > >>>Sanjay Limbikai
> > >>>
> > >>
> >
> >
>

Since the OP is comparing LINEST with the chart trendline, he must have
already plotted the data. On the assumption that he actually looked at
the plot that he produced, I can only assume that he is interested in
the numerics of the fitting process, not the quality (or lack thereof)
of this particular fit.

Jerry

Tushar Mehta wrote:

> If you plot the data you will quickly see that the only trend is a flat
> horizontal line or, with the application of a fair dose of imagination,
> a slight downward sloping trend.

I suppose so... {grin}

On a related topic, maybe you missed my post of the code for extracting
the trendline data to a worksheet. Or, maybe you didn't but have been
busy. In any case, the link:

http://groups.google.com/group/micro...rting/msg/0eda
30f29434786d?dmode=source&hl=en

And, of course, you are welcome to use either email or the NG for
followups.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <4337FBD1.3090200@no_e-mail.com>, post_a_reply@no_e-mail.com
says...
> Since the OP is comparing LINEST with the chart trendline, he must have
> already plotted the data. On the assumption that he actually looked at
> the plot that he produced, I can only assume that he is interested in
> the numerics of the fitting process, not the quality (or lack thereof)
> of this particular fit.
>
> Jerry
>
> Tushar Mehta wrote:
>
> > If you plot the data you will quickly see that the only trend is a flat
> > horizontal line or, with the application of a fair dose of imagination,
> > a slight downward sloping trend.
>
>

I don't recall seeing it the first time around. Thank you for pointing
it out. I will review and comment.

Jerry

Tushar Mehta wrote:

> I suppose so... {grin}
>
> On a related topic, maybe you missed my post of the code for extracting
> the trendline data to a worksheet. Or, maybe you didn't but have been
> busy. In any case, the link:
>
> http://groups.google.com/group/micro...rting/msg/0eda
> 30f29434786d?dmode=source&hl=en
>
> And, of course, you are welcome to use either email or the NG for
> followups.