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

 

Return to Excel Tips & Tricks

 

January 2006