Polynomial trend line formula

Topic

New Reply

In Excel 2000 is there a way to get the values from a Polynomial trend line? Someone is wanting to create the trend line in a chart and the use just the values from the trend line on a data sheet. I suggested just reading the values from the chart and manually entering this but it is for several charts with many data points.

Reply | Quote

Replies

Actually, there is a way to do it, using linest or trend, but you have to add columns with the power of X’s up to the degree of the polynomial you want to fit. Then you can calculate a polynomial by using linest and pretend that you are doing multiple regression.

={LINEST(C2:C7;A2:B7;TRUE;TRUE)}

This is an array function, so you must select the region for the ouput; then start the function wizard, enter the data and press ctrl-shift-enter for the output. The help of Excel can tell you where you can find the coefficients for the fit (actually the (1,1) element is the coefficient for the highest power of x). You can use the function Trend in the same way. If you want the coefficients separately, you can use = index(linest(…),1,1)

Reply | Quote