Copy a polynomial from a chart to a worksheet


Posted by Jesper from Sweden on October 13, 2001 2:51 AM

Here's a problem that I am often faced with.

From a series of x-values and y-values I create a chart as XY-scatter. I insert a trendline, usually a polynomial of the second or third degree. If I want to use this polynomial on a worksheet I copy it as text and change "x" to the cell I want to use as x-value. If I change the values that the trendline is based on, I have to copy it to the worksheet again.

Now to my question. Is it possible to link the trendline to a worksheet so it gets automatically updated? Or is it possible to calculate a polynomial from a series of data directly on the worksheet without using a chart?

Thanks



Posted by Alistair Eberst on October 13, 2001 6:30 AM

Given x-values in A2:A10 and y-values in B2:B10

try =LINEST(B2:B10,A2:A10^{1,2,3})

autofill this across 4 cells (horizontally) and enter it as an array formula - this should provide the 4 coefficients you're looking for, for a cubic. The formula can be adapted for a quadratic solution by removing ',3'