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 |
For details see http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm
January 2006