Differentiating a 6 order polynomial

[prodrive]

Hi,

I am trying to calculate the gradient of a 6 order trendline. I have recreated the trendline equation in a worksheet however I can't work out how to differentiate the line equation to give me a single gradient figure (worked out how to get the gradient at a specific point on the curve, however not for the whole curve). How can I manipulate excel's functions to get the gradient of a 6 order polynomial?

Any help would be appreciated.

Many thanks

 

[shg]

I think he's saying that the derivative gives you the slope at every point in the domain, as opposed to doing it numerically for the tabulated points.

 

[Stiuart_W]

Well I'm obviously being a bit thick but as there's an infinite number of those I'm not sure how we're supposed to list them

 

[shg]

I didn't see anything about listing them.

 

[joeu2004]

If I were to use LINEST or LOGEST how would I utilise the formula to get the gradient for a six order poly

Suppose your x values are in X1:X12 and your y values are in Y1:Y12.

For an order-6 polynomial, select 7 contiguous cells in a column, e.g. A1:A7, and array-enter the following formula (press ctrl+shift+Enter instead of just Enter):

=TRANSPOSE(LINEST(Y1:Y12,X1:X12^{1,2,3,4,5,6}))

As a double-check, put the following normally-entered formula (just press Enter as usual) into Z1 and copy down through Z12:

=SUMPRODUCT($A$1:$A$7,X1^{6;5;4;3;2;1;0})

Graph columns X, Y and Z using a scatter chart, and compare the curve for the z data with the order-6 polynomial trendline for the y data.

They should be very similar, even if the LINEST and trendline coefficients are very different.

(Actually, for the data that you posted, the coefficients are very similar up to 7 decimal places.)

-----

Alternatively, you could select 7 contiguous cells in a row, e.g. A1:G1. Then the formulas would be:

=LINEST(Y1:Y12,X1:X12^{1,2,3,4,5,6})

=SUMPRODUCT($A$1:$A$7,X1^{6,5,4,3,2,1,0})

Remember to array-enter the LINEST formula.

Whether to use semicolon for row separators and comma for column separators or vice versa depends on your Regional and Language Options control panel settings.

 

[shg]

copy down through Z12:

=SUMPRODUCT($A$1:$A$7,X1^{6;5;4;3;2;1;0})

Or =SERIESSUM(X1, 6, -1, $A$1:$A$6) + $A$7 to avoid a #NUM error for x=0.