Polynomial regression. How can I fit my X, Y data to a polynomial using LINEST?

As can be seem from the trendline in the chart below, the data in A2:B5 fits a third order polynomial. You wish to have the coefficients in worksheet cells as shown in A15:D15 or you wish to have the full LINEST statistics as in A17:D21

 Note: when the data is in rows rather than columns the array for the powers of x must be a ‘row array’. This is achieved by the use of semi-colons as separator. For example =LINEST(ystuff, xstuff^{1;2;3}) For convenience, the ranges A2:A5 and B2:B5 have been named "x" and "y" respectively. Select A15:D15 (you need four columns for the three coefficients plus the intercept), enter the formula =LINEST(y, x^{1, 2, 3}) and press SHIFT+CTRL+ENTER. If you wish to work without range names, use =LINEST(B2:B5,A2:A5^{1, 2, 3}).

For the full statistics, select a range of 5 rows by 4 columns, use the formula   =LINEST(y, x^{1, 2, 3}, , TRUE) and complete it with SHIFT+CTRL+ENTER.

LINEST may be used to fit data to other functions:

 Function Expression Excel formula Logarithmic y = aLn(x) + b =LINEST(y-values, LN(x-values)) Gives a and b Power y = axb =LINEST(LN(y-values), LN(x-values)) Gives Ln(a) and b Exponential base b y = abx =LINEST(LN(y-values), x) Gives Ln(a) and Ln(b) Exponential base e y = aex or y = aexp(x) =LINEST(LN(y-values), x) Gives Ln(a) and b January 2006