Cubic coefficients using LINEST

[johnsonlort]

Hi,

I am trying to LINEST to return coefficients using a cubic function in Excel 2013 but I am getting wrong results for two of the coefficients. Using the builtin Chart Trendline produces correct coefficient for my regression but LINEST (and Data Analysis Regression for that matter) produces wrong results according Microsoft (https://support.microsoft.com/en-us/kb/828533) due to collinearity.

This is my data:

x	y
2006	7798
2007	8027
2008	9526
2009	11661
2010	16014
2011	18731
2012	23405
2013	25294
2014	28578

<tbody>
</tbody>

Results:

Coef	Chart Trendline	LINEST
x3	-62.295<strike></strike>	-62.295<strike></strike>
x2	1098.254	163.834<strike></strike>
x1	-2746.214	3564.226<strike></strike>
intercept	9528.659	15467.104<strike></strike>
	CORRECT	X3 correct, rest WRONG

<tbody>
</tbody>

My first LINEST looks like this:

={LINEST(y;(x-AVERAGE(x))^{1,2,3})}

I have tried with a second LINEST function it also only calculates x3 correctly but not the x2, x1 and intercept.

=MMULT(LINEST(y;(x-AVERAGE(x))^{1,2,3});IFERROR(COMBIN({3;2;1;0};{3,2,1,0})*(-AVERAGE(x))^({3;2;1;0}-{3,2,1,0});0))

Any help is appreciated - maybe my formula are wrong?

 

[johnsonlort]

I managed to solve it. Apparently using Line, Bar and Column charts to produce coeffiecients for polynomia of "n>1" degrees will produce wrong coefficients since Line, Bar and Column charts treat the x-data as linear. So, if you are working with quadratic, cubic and higher order functions ALWAYS use an XY Scatter Plot if you want Excel to calculate the coefficients for you.

The second LINEST code is correct:

={MMULT(LINEST(y;(x-AVERAGE(x))^{1,2,3});IFERROR(COMBIN({3;2;1;0};{3,2,1,0})*(-AVERAGE(x))^({3;2;1;0}-{3,2,1,0});0))}

Indexing can of course also be used:

=INDEX(MMULT(LINEST(y;(x-AVERAGE(x))^{1,2,3});IFERROR(COMBIN({3;2;1;0};{3,2,1,0})*(-AVERAGE(x))^({3;2;1;0}-{3,2,1,0});0));1)  ' will produce x3
=INDEX(MMULT(LINEST(y;(x-AVERAGE(x))^{1,2,3});IFERROR(COMBIN({3;2;1;0};{3,2,1,0})*(-AVERAGE(x))^({3;2;1;0}-{3,2,1,0});0));2)  ' will produce x2
=INDEX(MMULT(LINEST(y;(x-AVERAGE(x))^{1,2,3});IFERROR(COMBIN({3;2;1;0};{3,2,1,0})*(-AVERAGE(x))^({3;2;1;0}-{3,2,1,0});0));3)  ' will produce x1
=INDEX(MMULT(LINEST(y;(x-AVERAGE(x))^{1,2,3});IFERROR(COMBIN({3;2;1;0};{3,2,1,0})*(-AVERAGE(x))^({3;2;1;0}-{3,2,1,0});0));4)  ' will produce intercept
=INDEX(LINEST(y;(x-AVERAGE(x))^{1,2,3};TRUE;TRUE);3) ' will produce r2

Hope somebody else will benefit from this