Quadratic and cubic regression in Excel using LINEST
<p>I am trying to use <code>LINEST</code> in Excel 2013 to get the coefficients for a cubic function but LINEST does not work well with non-linear functions according to this link <a href="http://support.microsoft.com/en-us/kb/828533" rel="nofollow noreferrer">MS KB828533</a>. Apparently this is because of the way collinearity is handled. A similar question is asked here <a href="https://stackoverflow.com/questions/10857252/quadratic-and-cubic-regression-in-excel">Quadratic and cubic regression in Excel</a> but it does not address the problem.</p>

<p>Excel's builtin <code>Column Chart | Trendline (3rd degree poly)</code> produces correct coefficients. However, <code>LINEST</code> as well as <code>Data Analysis | Regression</code> both give wrong coefficients.</p>

<p>EDIT: Excel's builtin <code>Column Chart</code> does <strong>NOT</strong> produce correct coefficients for polynomials. Only use <code>Column Chart</code> trendline for linear data! (please see answer).</p>

<p>This is my data:</p>

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

<p>I can only get the third coefficent (here <code>x3</code>) using this:</p>

<pre><code>={LINEST(y;(x-AVERAGE(x))^{1,2,3})}
</code></pre>

<p>Results:</p>

<pre>
Coef    Chart       LINEST
x3      -62.295     -62.295
x2      1098.254    163.834
x1      -2746.214   3564.226
intcpt  9528.659    15467.104
        CORRECT     x3 correct, rest WRONG
</pre> 

<p>I have also tried a more complex <code>LINEST</code> like this:</p>

<pre><code>={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))}
</code></pre>

<p>But in similar fashion only <code>x3</code> is correct and the rest is wrong.</p>

<p>Any help is appreciated.</p>

<p><a href="https://i.stack.imgur.com/i4fiI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/i4fiI.png" alt="Excel LINEST for non-linear function"></a></p>