Differences between Chart Trendline and LINEST 4th order polynomial

M

Movyk

New Member
Joined
May 1, 2018
Messages
2
Dear all,

I am currently processing 1D data formed by two vectors x,y. I have been calculating the 4th order polynomial that better fits the data. I have a matlab code (I am using polyfit3 function) which provides me with the polynomial equation, which matches with the Excel chart trendline obtained from Excel.

Data:

[TABLE="width: 6113"]
<colgroup><col><col span="6"><col span="88"></colgroup><tbody>[TR]
[TD="align: right"]7,1998204[/TD]
[TD="align: right"]7,157007[/TD]
[TD="align: right"]7,19982[/TD]
[TD="align: right"]7,19982[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,160575[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,206956[/TD]
[TD="align: right"]7,19982[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,171278[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,206956[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,16771[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,153439[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,16771[/TD]
[TD="align: right"]7,149871[/TD]
[TD="align: right"]7,178414[/TD]
[TD="align: right"]7,19982[/TD]
[TD="align: right"]7,19982[/TD]
[TD="align: right"]7,16771[/TD]
[TD="align: right"]7,171278[/TD]
[TD="align: right"]7,149871[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,185549[/TD]
[TD="align: right"]7,171278[/TD]
[TD="align: right"]7,174846[/TD]
[TD="align: right"]7,178414[/TD]
[TD="align: right"]7,185549[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,171278[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,185549[/TD]
[TD="align: right"]7,16771[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,19982[/TD]
[TD="align: right"]7,203388[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,142736[/TD]
[TD="align: right"]7,171278[/TD]
[TD="align: right"]7,139168[/TD]
[TD="align: right"]7,19982[/TD]
[TD="align: right"]7,146303[/TD]
[TD="align: right"]7,171278[/TD]
[TD="align: right"]7,146303[/TD]
[TD="align: right"]7,203388[/TD]
[TD="align: right"]7,206956[/TD]
[TD="align: right"]7,171278[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,174846[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,185549[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,185549[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,203388[/TD]
[TD="align: right"]7,160575[/TD]
[TD="align: right"]7,189117[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,121329[/TD]
[TD="align: right"]7,164142[/TD]
[TD="align: right"]7,192685[/TD]
[TD="align: right"]7,181981[/TD]
[TD="align: right"]7,160575[/TD]
[TD="align: right"]7,1356[/TD]
[TD="align: right"]7,171278[/TD]
[TD="align: right"]7,196253[/TD]
[TD="align: right"]7,149871[/TD]
[TD="align: right"]7,206956[/TD]
[TD="align: right"]7,19982[/TD]
[TD="align: right"]7,206956[/TD]
[TD="align: right"]7,117761[/TD]
[TD="align: right"]7,10349[/TD]
[TD="align: right"]7,114193[/TD]
[TD="align: right"]7,121329[/TD]
[TD="align: right"]7,139168[/TD]
[TD="align: right"]7,128464[/TD]
[TD="align: right"]7,1356[/TD]
[/TR]
[TR]
[TD="align: right"]0,014546247[/TD]
[TD="align: right"]0,00798[/TD]
[TD="align: right"]0,017741[/TD]
[TD="align: right"]0,014018[/TD]
[TD="align: right"]0,01317[/TD]
[TD="align: right"]0,007906[/TD]
[TD="align: right"]0,015615[/TD]
[TD="align: right"]0,01461[/TD]
[TD="align: right"]0,017049[/TD]
[TD="align: right"]0,013897[/TD]
[TD="align: right"]0,011463[/TD]
[TD="align: right"]0,017062[/TD]
[TD="align: right"]0,009842[/TD]
[TD="align: right"]0,015752[/TD]
[TD="align: right"]0,029294[/TD]
[TD="align: right"]0,006242[/TD]
[TD="align: right"]0,014203[/TD]
[TD="align: right"]0,006118[/TD]
[TD="align: right"]0,016105[/TD]
[TD="align: right"]0,013564[/TD]
[TD="align: right"]0,006242[/TD]
[TD="align: right"]0,008135[/TD]
[TD="align: right"]0,012995[/TD]
[TD="align: right"]0,011897[/TD]
[TD="align: right"]0,016939[/TD]
[TD="align: right"]0,008539[/TD]
[TD="align: right"]0,00934[/TD]
[TD="align: right"]0,007713[/TD]
[TD="align: right"]0,015167[/TD]
[TD="align: right"]0,013899[/TD]
[TD="align: right"]0,012797[/TD]
[TD="align: right"]0,01445[/TD]
[TD="align: right"]0,014515[/TD]
[TD="align: right"]0,013912[/TD]
[TD="align: right"]0,01508[/TD]
[TD="align: right"]0,014073[/TD]
[TD="align: right"]0,014163[/TD]
[TD="align: right"]0,01411[/TD]
[TD="align: right"]0,019884[/TD]
[TD="align: right"]0,01555[/TD]
[TD="align: right"]0,01555[/TD]
[TD="align: right"]0,012701[/TD]
[TD="align: right"]0,010708[/TD]
[TD="align: right"]0,010661[/TD]
[TD="align: right"]0,007544[/TD]
[TD="align: right"]0,008583[/TD]
[TD="align: right"]0,004867[/TD]
[TD="align: right"]0,005134[/TD]
[TD="align: right"]0,006416[/TD]
[TD="align: right"]0,008221[/TD]
[TD="align: right"]0,006028[/TD]
[TD="align: right"]0,008773[/TD]
[TD="align: right"]0,00773[/TD]
[TD="align: right"]0,015758[/TD]
[TD="align: right"]0,012053[/TD]
[TD="align: right"]0,025601[/TD]
[TD="align: right"]0,013883[/TD]
[TD="align: right"]0,01643[/TD]
[TD="align: right"]0,012639[/TD]
[TD="align: right"]0,014957[/TD]
[TD="align: right"]0,015798[/TD]
[TD="align: right"]0,01638[/TD]
[TD="align: right"]0,013487[/TD]
[TD="align: right"]0,014922[/TD]
[TD="align: right"]0,013214[/TD]
[TD="align: right"]0,012892[/TD]
[TD="align: right"]0,016029[/TD]
[TD="align: right"]0,015917[/TD]
[TD="align: right"]0,014866[/TD]
[TD="align: right"]0,01638[/TD]
[TD="align: right"]0,016863[/TD]
[TD="align: right"]0,013762[/TD]
[TD="align: right"]0,01112[/TD]
[TD="align: right"]0,007899[/TD]
[TD="align: right"]0,012697[/TD]
[TD="align: right"]0,01665[/TD]
[TD="align: right"]0,00752[/TD]
[TD="align: right"]0,012058[/TD]
[TD="align: right"]0,010209[/TD]
[TD="align: right"]0,008828[/TD]
[TD="align: right"]0,007103[/TD]
[TD="align: right"]0,00585[/TD]
[TD="align: right"]0,006641[/TD]
[TD="align: right"]0,010194[/TD]
[TD="align: right"]0,008422[/TD]
[TD="align: right"]0,010703[/TD]
[TD="align: right"]0,007492[/TD]
[TD="align: right"]0,017839[/TD]
[TD="align: right"]0,013032[/TD]
[TD="align: right"]0,010635[/TD]
[TD="align: right"]0,015363[/TD]
[TD="align: right"]0,00752[/TD]
[TD="align: right"]0,005638[/TD]
[TD="align: right"]0,006676[/TD]
[TD="align: right"]0,00585[/TD]
[/TR]
</tbody>[/TABLE]

Furthermore, the Rsquared from Matlab and Excel chart trendline match, at 42,4%.

The problem happens when I try to validate these data with LINEST.

LINEST is providing a 39,3% Rsquared, which I understood now is happening because LINEST is assuming one of the polynomial coefficients to be zero: (LINEST Equation: =LINEST(D34:CT34;D33:CT33^{1;2;3;4};TRUE;TRUE))

[TABLE="width: 351"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD="align: right"]-10,35839856[/TD]
[TD="align: right"]197,8485[/TD]
[TD="align: right"]-1062,82[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9086,93[/TD]
[/TR]
[TR]
[TD="align: right"]2,007179567[/TD]
[TD="align: right"]38,33005[/TD]
[TD="align: right"]205,8645[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1759,467[/TD]
[/TR]
[TR]
[TD="align: right"]0,392878808[/TD]
[TD="align: right"]0,003456[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]19,62923379[/TD]
[TD="align: right"]91[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
The same pattern occurs if I assume LINEST to provide me a 5th order polynomial: two of the coefficients are automatically assumed to be null.

The polynomial provided by both Matlab and Excel trendline chart has no null coefficients and are equal.

Does anybody knows why LINEST is assuming some of the coefficients to be zero?

I have validated this situation in both Excel 365 and Excel 2013.

Thank you very much

Mário Vieira
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
So, I think it is a problem with LINEST...

If I subtract 7,099922 to all the X values, the LINEST starts working perfectly.

Hope this helps someone.

Best regards
 
Upvote 0
I can replicate your issue, and your fix, but I can't explain it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Help with Trendline
Replies
4
Views
938
shg
shg
D
  • Question Question
How to find Y for known X on a 6th order polynomial trendline equation?
Replies
1
Views
7K
PaddyD
P
E
  • Question Question
Scatter Plot in Excel
Replies
2
Views
875
ExplorerOfExcel
E
B
  • Question Question
using linest function for 2nd order polynomial
Replies
9
Views
19K
pgc01
P
J
  • Question Question
Need help with a solver problem Don't know how to use slover and identify constraints.
Replies
0
Views
816
jdjames4
J

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top