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:

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]x[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]y[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2006[/TD]
[TD="bgcolor: transparent, align: right"]7798[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2007[/TD]
[TD="bgcolor: transparent, align: right"]8027[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2008[/TD]
[TD="bgcolor: transparent, align: right"]9526[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2009[/TD]
[TD="bgcolor: transparent, align: right"]11661[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2010[/TD]
[TD="bgcolor: transparent, align: right"]16014[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2011[/TD]
[TD="bgcolor: transparent, align: right"]18731[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2012[/TD]
[TD="bgcolor: transparent, align: right"]23405[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]25294[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2014[/TD]
[TD="bgcolor: transparent, align: right"]28578[/TD]
[/TR]
</tbody>[/TABLE]

Results:

[TABLE="width: 400"]
<tbody>[TR]
[TD]Coef
[/TD]
[TD]Chart Trendline
[/TD]
[TD]LINEST
[/TD]
[/TR]
[TR]
[TD]x3
[/TD]
[TD]-62.295<strike></strike>
[/TD]
[TD]-62.295<strike></strike>
[/TD]
[/TR]
[TR]
[TD]x2
[/TD]
[TD]1098.254
[/TD]
[TD]163.834<strike></strike>
[/TD]
[/TR]
[TR]
[TD]x1
[/TD]
[TD]-2746.214
[/TD]
[TD]3564.226<strike></strike>
[/TD]
[/TR]
[TR]
[TD]intercept
[/TD]
[TD]9528.659
[/TD]
[TD]15467.104<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]CORRECT
[/TD]
[TD]X3 correct, rest WRONG
[/TD]
[/TR]
</tbody>[/TABLE]

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