Help with Trendline

[jonny1984]

Hi all

Tearing my heair out all afternoon with this .....

I have a table of data which I am using a chart and then I want to use the trendline equation from the chart (polynomial formula) - in other cells.

I am using the INDEX / LINEST function .... but the function is giving me different coefficients from the ones listed on the chart in the trendline !!!!!!

Have uploaded the sheet to show what I am trying to achieve - I've gone over it 1000 times looking for mistakes but can't find any !! If anyone else could help it would be most gratefully appreciated !!

Excel file here
https://www.dropbox.com/s/xt6epglrcv6shh6/Book4.xlsx?dl=0

Thanks!
Jonny

 

[shg]

For whatever reason, charts and LINEST use different algorithms for regression. I expect they will converge at some point.

Using INDEX, you are requiring that the regression be performed repeatedly for each coefficient, which is computer abuse. You can get all in one go with additional stats, ...

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

26​

[/td][td]

0.0002281​

[/td][td]

0.018689​

[/td][td]

0.433862​

[/td][td][/td][td]B26:D30: {=LINEST(C3:C21, B3:B21^{1,2},,TRUE)}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

27​

[/td][td]

0.0001002​

[/td][td]

0.001497​

[/td][td]

0.03612​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

28​

[/td][td="bgcolor:#CCFFCC"]

0.9069757​

[/td][td]

0.105011​

[/td][td]

#N/A​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

29​

[/td][td]

77.999067​

[/td][td]

16​

[/td][td]

#N/A​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

30​

[/td][td]

1.7202441​

[/td][td]

0.176437​

[/td][td]

#N/A​

[/td][td][/td][td][/td][/tr]
[/table]

(The green cell is R2)

Or all in one go with just the coefficients:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

32​

[/td][td]

0.0002281​

[/td][td]

0.018689​

[/td][td]

0.433862​

[/td][td][/td][td]B32:D32: {=LINEST(C3:C21, B3:B21^{1,2})}[/td][/tr]
[/table]

 

[jonny1984]

For whatever reason, charts and LINEST use different algorithms for regression. I expect they will converge at some point.

Using INDEX, you are requiring that the regression be performed repeatedly for each coefficient, which is computer abuse. You can get all in one go with additional stats, ...

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[TD="bgcolor: #C0C0C0"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

26​

[/TD]
[TD]

0.0002281​

[/TD]
[TD]

0.018689​

[/TD]
[TD]

0.433862​

[/TD]
[TD][/TD]
[TD]B26:D30: {=LINEST(C3:C21, B3:B21^{1,2},,TRUE)}[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

27​

[/TD]
[TD]

0.0001002​

[/TD]
[TD]

0.001497​

[/TD]
[TD]

0.03612​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

28​

[/TD]
[TD="bgcolor: #CCFFCC"]

0.9069757​

[/TD]
[TD]

0.105011​

[/TD]
[TD]

#N/A​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

29​

[/TD]
[TD]

77.999067​

[/TD]
[TD]

16​

[/TD]
[TD]

#N/A​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

30​

[/TD]
[TD]

1.7202441​

[/TD]
[TD]

0.176437​

[/TD]
[TD]

#N/A​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


(The green cell is R2)

Or all in one go with just the coefficients:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[TD="bgcolor: #C0C0C0"]

C​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[TD="bgcolor: #C0C0C0"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

32​

[/TD]
[TD]

0.0002281​

[/TD]
[TD]

0.018689​

[/TD]
[TD]

0.433862​

[/TD]
[TD][/TD]
[TD]B32:D32: {=LINEST(C3:C21, B3:B21^{1,2})}[/TD]
[/TR]
</tbody>[/TABLE]

Hi shg

Thanks for the advice -

Noted re removing INDEX - have changed to use your formulas

Noted re different algorithms - but then I would have 2 furhter questions....

(1) How can the algorithms be so different ?? The "intercept" (b) in the formula on the graph is 0.0715 - which also looks to be correct, this is where the trendline bisects the y-axis. However, the intercept in the formula is 0.434 !!! This is obviously very different from 0.0715 (43% vs 7% on a y-axis chart) - and it also looks to be a completely random number ! This makes me think something has gone wrong .

(2) If it hasn't gone wrong and this is genuinely the result of another algorithm's estimation - is there any way to retrieve the numbers from the trendline chart.. i.e. is there any other formula? Or what calculation is excel doing to calculate these coefficients for the formula on the chart ?


Thanks in advance
Jonny

 

[shg]

There's no need to quote my posts back to me.

They are two different fits, and R2 is similar for both.

I recollect Andy Pope has some code to retrieve the coefficients from the trendline equation; you'd need to track it down: http://andypope.info/

 

[shg]

D'oh.

Your chart is a line chart, which means the x axis is treated as categorical {1,2,3,...}.

Change it to x-y scatter.