# 4th Order Polynomials - Please Help!

#### MickRose

##### New Member
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

Last edited:

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### diddi

##### Well-known Member
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

OK, done.

Cheers.

#### shg

##### MrExcel MVP
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.

Replies
0
Views
305
Replies
10
Views
640
Replies
0
Views
212
Replies
3
Views
589
Replies
1
Views
1K

Threads
1,182,047
Messages
5,933,394
Members
436,892
Latest member
tommsegers

### 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

### 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