Differences between Chart Trendline and LINEST 4th order polynomial

[Movyk]

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:

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

<colgroup><col><col span="6"><col span="88"></colgroup><tbody>
</tbody>

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))

-10,35839856	197,8485	-1062,82	0	9086,93
2,007179567	38,33005	205,8645	0	1759,467
0,392878808	0,003456	#N/A	#N/A	#N/A
19,62923379	91	#N/A	#N/A	#N/A

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

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

 

[Movyk]

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

 

[Jon Peltier]

I can replicate your issue, and your fix, but I can't explain it.