4th Order Polynomials - Please Help!

MickRose

New Member
Joined
May 15, 2008
Messages
5
Hi guys,

I'm having a lot of trouble with a spreadsheet I need to make for work. Essentially a test we carry out involves plotting 4 or 5 points on a chart, placing a polynomial trendline and reading the maximum y value from the curve.


To simplify this, I've successfully written formulas that calculate the coefficients of 2nd and 3rd order polynomials, which fills out a table, and shows me the MAX value. Easy.


However, my 4th order polynomial (the one I really want) for some reason follows my data curve perfectly, but then curves back up, throwing my result out the window. I'm using x values of 1 - 40 and the curve turns upwards at around x = 15 (see below).


4thorderpolynomial.jpg


I'm using this formula:


4th Order: y = (c4 * x^4) + (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b


Where,


c4: =INDEX(LINEST(y,x^{1,2,3,4}),1)

c3: =INDEX(LINEST(y,x^{1,2,3,4}),1,2)
c2: =INDEX(LINEST(y,x^{1,2,3,4}),1,3)
c1: =INDEX(LINEST(y,x^{1,2,3,4}),1,4)
b: =INDEX(LINEST(y,x^{1,2,3,4}),1,5)

Here is my data:


POINT x y

<table border="0" cellpadding="0" cellspacing="0" width="209"><col style="width: 53pt;" width="71"> <col style="width: 52pt;" span="2" width="69"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 53pt;" height="20" width="71">1</td> <td class="xl67" style="border-left: medium none; width: 52pt;" width="69">2.27</td> <td class="xl68" style="border-left: medium none; width: 52pt;" width="69">1.934</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">4.59</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">2.045</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">3</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">6.55</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">2.100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">4</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">8.38</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">2.096</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; border-top: medium none;" height="17">5</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">9.70</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">2.056</td> </tr> </tbody></table>
So, my coefficients are:


c4 = 0.000065565

c3 = -0.002017188
c2 = 0.014354374
c1 = 0.011358541
b = 1.856182718

I know I'm on the right track, as the calculated figures match the actual figures perfectly.


So why does this curve back upwards?


Any help would be greatly appreciated. I know I'm at the right place to solve this!


Cheers,


Mick



 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,302
Office Version
  1. 2010
Platform
  1. Windows
polys of order 4 adopt a clasic "W" shape, except when there are repeated solutions. if you don't want that shape then dont choose x^4, its like using a parabola to approximate a straight line.

higher order polys are more unreliable as extrapolation tools than lower order or e^x, but better at interpolation
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,785
Office Version
  1. 2010
Platform
  1. Windows
Give any N+1 points with unique values of X, you can always make a perfect fit with a polynomial of order N; it doesn't tell you anything about the data, unless you knew beforehand that it was a fourth order polynomial and just wanted to know which one.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top