4th Order Polynomials - Please Help!

[MickRose]

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).

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

 

[diddi]

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

 

[MickRose]

OK, done.

Cheers.

 

[shg]

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.