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

 

[Andrew Poulsom]

Welcome to MrExcel.

This may help you:

Spreadsheet Page Excel Tips: Chart Trendline Formulas

 

[Stiuart_W]

Whilst it is nearly 3 decades since I differentiated anything I think even I could differentiate a 6th order (or nth order for that matter) polynomial.

Surely if you have all the co-efficients (and you said you have) it is pretty easy to differentiate and calculate the gradient at a given point?


If you have a specific problem then please post that

 

[prodrive]

Whilst it is nearly 3 decades since I differentiated anything I think even I could differentiate a 6th order (or nth order for that matter) polynomial.

Surely if you have all the co-efficients (and you said you have) it is pretty easy to differentiate and calculate the gradient at a given point?


If you have a specific problem then please post that

Thanks for you post. I can find the gradient of a curve at specific point relatively easily. The issue is differentiating the the curve as a whole.

The issue is how to factorise the line equation then solve the equation for y=0. The current line equation I have is:
y=2.E-05x^6-0.00725x^5+0.014260x^4-0.226488x^3+2.056124x^2-8.606512x+75.164801.

It may be easy, however my excel knowledge isn't good enough to factorise this then solve for y=0. I do need an automated way of doing this as I will have several hundred of these equations to calculate. My only thought would be doing a VBA loop to try and solve this but that's my only idea.

Thanks for your help so far.

 

[Stiuart_W]

Well I've done a bit of research and you can use the Excel function LINEST to obtain the polynomial co-efficients. This site gives a good explanation of what to do Excel’s LINEST Function: Little Things Can Make a Big Difference | A Field Perspective on Engineering

I did a quick test and noted that the co-efficients displayed in the Excel chart and those returned by LINEST are quite different but it still produces a curve that follows very closely the Excel graph. So you could use this to obtain your factors for the differential and return the gradient for a given X

 

[shg]

To get the coefficients of the derivative,

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td]

exp​

[/td][td]

6​

[/td][td]

5​

[/td][td]

4​

[/td][td]

3​

[/td][td]

2​

[/td][td]

1​

[/td][td]

b​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

coeff​

[/td][td]

0.000020​

[/td][td]

-0.007250​

[/td][td]

0.014260​

[/td][td]

-0.226488​

[/td][td]

2.056124​

[/td][td]

-8.606512​

[/td][td]

75.164801​

[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]

derivative​

[/td][td][/td][td]

0.000120​

[/td][td]

-0.036250​

[/td][td]

0.057040​

[/td][td]

-0.679464​

[/td][td]

4.112248​

[/td][td]

-8.606512​

[/td][td]C3 and across: =B1*B2[/td][/tr]
[/table]

To find the zeros (there are up to 5), you'll need to use Solver.

Given that the coefficient of x^6 only has one significant digit, it looks like you took the coefficients from the trendline on a graph. You'd be better off using LINEST.

 

[joeu2004]

The current line equation I have is:

y=2.E-05x^6 - 0.00725x^5 + 0.014260x^4 - 0.226488x^3 + 2.056124x^2 - 8.606512x + 75.164801

I do not have time to help you with the larger problem. But I think it is important to note the following.

Do not use the coefficients constants as they appear by default in the trendline formula.

Any arithmetic based on those rounded values is likely to be off significantly.

At a minimum, format the "trendline label" to display coefficients in Scientific format with 14 decimal places. That will ensure that you use values with 15 significant digits of precision, the best that Excel will format.

Even better: use LINEST (or LOGEST) to generate "the coefficients". Two caveats:

1. LINEST and chart trendlines do not always produce the same coefficients. Apparently, there numerical methods for regression are not identical.

2. LINEST sometimes returns an error or invalid coefficients (typically zero) when chart trendlines work properly. Part of the reason for that is: the input to LINEST is an array of values raised to powers up to 6 (in your case), which can encounter rounding error and computational limitations. In contrast, the chart trendline algorithm works the original data. Its internal numerical algorithms can avoid or handle such errors.

 

[prodrive]

Thanks everyone for your help on this. The comments on the accuracy of the coefficients are very valid and something I will take on board once I have worked out how to do the problem in hand.

If I were to use LINEST or LOGEST how would I utilise the formula to get the gradient for a six order poly - as far as I can see they can only two a x^2 curve? I have a spreadsheet that I would like to share with you all to see where I have got to, however I can't find a way to upload this to the thread.

To be clear, I am interested in the gradient of the whole line and not at specific point on the line (I have included these numbers anyway in case it assists with the calculation).

This table are the coefficients of the trendline (these do match the trendline that excel produces when a 6 ord poly is selected) that have been calculated from the x,y coordinates

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]Line Equation Coefficients[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]cc[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]y=[a]x^6+x^5+[cc]x^4+[d]x^3+[e]x^2+[f]x+[g][/TD]
[TD]2.E-05[/TD]
[TD]-0.000725[/TD]
[TD]0.014260[/TD]
[TD]-0.226488[/TD]
[TD]2.056124[/TD]
[TD]-8.606512[/TD]
[TD]75.164801[/TD]
[/TR]
</tbody>[/TABLE]

This table is the Raw Data (plus the line of best fit equation figure and the respective slope at the point)

[TABLE="width: 500"]
<tbody>[TR]
[TD]X Axis[/TD]
[TD]Y Axis[/TD]
[TD]Equation[/TD]
[TD]Slope at point[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]68.48692793[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]68.40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-5.12017502138305[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]64.30623931[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]64.57[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]-2.69712153585393[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]62.73016414[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]62.73[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]-1.10488484685025[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]62.82180783[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]62.14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]-0.16488455749730[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]61.92702315[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]62.23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]0.26410980605998[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]61.83759786[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]62.54[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]0.30251542103434[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]62.688179[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]62.74[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]0.06647279437361[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]64.12595884[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]62.63[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]-0.31561761086201[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]61.16592851[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]62.12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]-0.68655874912258[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]60.94477071[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]61.32[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]-0.84382036599111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]61.1231282[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]60.59[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]-0.52300237180615[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]60.41032497[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 125"]60.55[/TD]
[TD="class: xl65, width: 125, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 165"]
<tbody>[TR]
[TD="class: xl66, width: 165, align: right"]0.61870178471540[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

You can put the workbook on box.net and post a link.

 

[Stiuart_W]

Sorry but I don't understand what you mean by this "......I am interested in the gradient of the whole line and not at specific point.....". To me that doesn't make sense as the line isn't straight so it's gradient is different at every point along it.