Returning a data point off a Polynominal Trendline

[Archer99]

This is the first time I have posted here so you will have to bear with me for a moment. I work with shipping containers and I am interested in determining how many slots I can fill on board a vessel with a specific cargo mix. The catch is that while 20' units can fit anywhere, 40' units can only fit in specific areas on board. I have extracted some historical data that shows how many 20' units will normally fit in addition to a specific number of 40' units. So, with 40' units as my point of reference, the results come up as this:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]20'
[/TD]
[TD="class: xl65, width: 64"]40'[/TD]
[/TR]
[TR]
[TD="class: xl66"]304[/TD]
[TD="class: xl68"]0[/TD]
[/TR]
[TR]
[TD="class: xl66"]288[/TD]
[TD="class: xl67"]20[/TD]
[/TR]
[TR]
[TD="class: xl66"]260[/TD]
[TD="class: xl67"]40[/TD]
[/TR]
[TR]
[TD="class: xl66"]226[/TD]
[TD="class: xl67"]60[/TD]
[/TR]
[TR]
[TD="class: xl66"]162[/TD]
[TD="class: xl67"]80[/TD]
[/TR]
</tbody>[/TABLE]

(ie. If I want to load 60 x 40' units then I can only load 226 x 20' as additional cargo around them)

After extracting the equation from the graph I get this:
y = -7.2857x2 + 9.1143x + 300.8

So my question is this - If I want to create an open cell where I can enter the number of 40's to be loaded (eg. 46), how can I input this equation into a formula to determine how may 20' slots will be available off the data provided above?

I have used FORECAST and TREND but these seem to only follow Linear equations. I am rather stumped so I would be grateful if anyone had any ideas to solve this one.

 

[joeu2004]

After extracting the equation from the graph I get this:
y = -7.2857x2 + 9.1143x + 300.8

So my question is this - If I want to create an open cell where I can enter the number of 40's to be loaded (eg. 46), how can I input this equation into a formula to determine how may 20' slots will be available off the data provided above?

First, that trendline formula is incorrect. My guess is: you created a line chart, then displayed the formula for a 2-degree polynomial trendline. The problem is: in a line chart, the trendline formula always assumes that x is 1, 2, 3 etc, not the values that you might have assigned to the x-axis.

Instead, you should create an scatter chart. Then the formula for the 2-degree polynomial trendline appears to be about

-0.0182*x^2 - 0.2729*x + 302.63.

Second, when you copy the coefficients from the chart trendline formula ("label"), it is important to format the trendline label so that it displays sufficient precision. Generally, I suggest using the format Scientific with 14 decimal places. That works regardless of the magnitude of the coefficients, which can vary widely (albeit not so much in your case). Thus, the formula for the polynomial trendline appears to be:

-1.82142857142857E-02*x^2 - 2.72857142857134E-01*x + 3.02628571428571E+02

Finally, since you have only 5 pairs of data, you can use a 4-degree polynomial trendline to fit the data exactly.

That said, usually it is possible to use LINEST to generate the polynomial coefficients directly in the worksheet, without the need to copy them from the chart trendline label. For example:

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD="align: right"]x
[/TD]
[TD="align: right"]y[/TD]
[TD="align: right"]est y[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]304[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]a1[/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]288[/TD]
[TD="align: right"]288[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]a2[/TD]
[TD="align: right"]0.0010625000[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]a3[/TD]
[TD="align: right"]-0.0568750000[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]226[/TD]
[TD="align: right"]226[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]a4[/TD]
[TD="align: right"]-0.0250000000[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]a5[/TD]
[TD="align: right"]304.0000000000[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]3.0161[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
C2 :     =SERIESSUM(A2,4,-1,$F$2:$F$5) + $F$6
F2:F6 :  { =TRANSPOSE(LINEST(B2:B6, A2:A6^{1,2,3,4})) }

Formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

PS.... You might notice that I reversed the columns of data: 40' units (x) on the left; 20' units on the right. That is more convenient for creating the scatter chart. But it is not necessary. Your original order work as well.

Copy C2 into C3:C7.

Thus, we can estimate the number of 20' units (est y) in column C by entering any number of 40' units (x) into the corresponding cell in column A

FYI, we cannot use SERIESSUM(...,F2:F6) because for x=0, which is part of the data, SERIESSUM would try to evaluate 0^0, wihch results in a #NUM error.

Also, LINEST returns a row of coefficients. I use TRANSPORT to return a column of coefficients. That's a personal choice. Alternatively, you can array-enter the LINEST formula into F2:J2, for example.

Generally, polynomial trendline (even the 2-degree polynomial) should be used only to interpolate data between the endpoints; in your case, between x=0 and x=80. Outside those limits, the polynomial trendline might have surprising behavior.

However, in your case, I was able to extend the 4-degree polynomial trendline by 40 units to x=100 with reasonable results, or so it appears. (Only you can determine what is and is not "reasonable".)

In fact, for x=101, est y=3 (about), as demonstrated in C7. For x=102 or more, est y becomes negative, which is not practical in your case.

 

[Archer99]

Hi joeu2004. Thanks for the in depth reply! Unfortunately a lot of the maths in this is well beyond my understanding so I could only follow it up to the table that you provided. I believe that I have inputted the formulas correctly in column C and F, however I am ending up with this:


-- removed inline image ---

-- removed inline image ---


I'm guessing that there is a step I'm missing here, and maths at this level was never my strong point. I would be grateful if you could please explain what I should be doing from here. Thank you!

 

[Archer99]

Okay, I guess this forum doesn't like screen shots. Try this!

[TABLE="width: 496"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Est. Y[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]304[/TD]
[TD]-0.0000078125[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]288[/TD]
[TD]-1.3157890625[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]260[/TD]
[TD]-20.5128203125[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]226[/TD]
[TD]-102.9661015625[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]162[/TD]
[TD]-324.0506328125[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD] [/TD]
[TD]-821.1016015625[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[joeu2004]

Formulas:
C2 : =SERIESSUM(A2,4,-1,$F$2:$F$5) + $F$6
F2:F6 : { =TRANSPOSE(LINEST(B2:B6, A2:A6^{1,2,3,4})) }

I'm guessing that there is a step I'm missing here

[TABLE="width: 496"]
<tbody>[TR]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Est. Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]304[/TD]
[TD]-0.0000078125[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]288[/TD]
[TD]-1.3157890625[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]260[/TD]
[TD]-20.5128203125[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]226[/TD]
[TD]-102.9661015625[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]162[/TD]
[TD]-324.0506328125[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-0.0000078125[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[TD]-821.1016015625[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Yes: You omitted the TRANSPOSE function. See above.

If you prefer array-enter just =LINEST(...) without TRANSPOSE, select F2:J2 instead. Also, be sure to replace $F$2:$F$6 with $F$2:$J$2 in the SERIESSUM function.

This also gives me an opportunity to embellish my previous explanation. I labeled F2:F6 as "a1", "a2" etc. So the SERIESSUM function represents the math formula:

estY = a1*x^4 + a2*x^3 + a3*x^2 + a4*x + a5

Hope that helps. Feel free to ask any other questions.

 

[Archer99]

Yes: You omitted the TRANSPOSE function. See above.

If you prefer array-enter just =LINEST(...) without TRANSPOSE, select F2:J2 instead. Also, be sure to replace $F$2:$F$6 with $F$2:$J$2 in the SERIESSUM function.

This also gives me an opportunity to embellish my previous explanation. I labeled F2:F6 as "a1", "a2" etc. So the SERIESSUM function represents the math formula:

estY = a1*x^4 + a2*x^3 + a3*x^2 + a4*x + a5

Hope that helps. Feel free to ask any other questions.

I did include the TRANSPOSE function in the original post but there is definitely something being lost in translation. To simplify things (hopefully ) I am going to show what I have entered in the cells so far:

[TABLE="class: grid, width: 900, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]x
[/TD]
[TD]y
[/TD]
[TD][/TD]
[TD][TABLE="width: 332"]
<colgroup><col width="332"></colgroup><tbody>[TR]
[TD="class: xl65, width: 332"]Formula Used in Colum C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 400"]
<colgroup><col width="349"></colgroup><tbody>[TR]
[TD="width: 349"]Formula Used in Column F
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]304
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135"]-0.0000078125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 332"]
<colgroup><col></colgroup><tbody>[TR="class: grid"]
[TD]<--- = SERIESSUM(A2,4,-1,$F$2:$F$5)+$F$6
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135, align: right"]-0.0000078125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 420"]
<colgroup><col></colgroup><tbody>[TR]
[TD]<--- { =TRANSPOSE(LINEST(B2:B6,A2:A6^{1,2,3,4})) }
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]20
[/TD]
[TD]288
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135"]-1.3157890625[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 332"]
<colgroup><col></colgroup><tbody>[TR="class: grid"]
[TD]<--- = SERIESSUM(A3,4,-1,$F$2:$F$5)+$F$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135, align: right"]-0.0000078125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 420"]
<colgroup><col></colgroup><tbody>[TR]
[TD]<--- { =TRANSPOSE(LINEST(B2:B6,A2:A6^{1,2,3,4})) }
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]40
[/TD]
[TD]260
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135"]-20.5128203125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 332"]
<colgroup><col></colgroup><tbody>[TR="class: grid"]
[TD]<--- = SERIESSUM(A4,4,-1,$F$2:$F$5)+$F$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135, align: right"]-0.0000078125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 420"]
<colgroup><col></colgroup><tbody>[TR]
[TD]<--- { =TRANSPOSE(LINEST(B2:B6,A2:A6^{1,2,3,4})) }
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]60
[/TD]
[TD]226
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135"]-102.9661015625[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 332"]
<colgroup><col></colgroup><tbody>[TR="class: grid"]
[TD]<--- = SERIESSUM(A5,4,-1,$F$2:$F$5)+$F$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135, align: right"]-0.0000078125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 420"]
<colgroup><col></colgroup><tbody>[TR]
[TD]<--- { =TRANSPOSE(LINEST(B2:B6,A2:A6^{1,2,3,4})) }
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]80
[/TD]
[TD]162
[/TD]
[TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135"]-324.0506328125[/TD]
[/TR]
</tbody>[/TABLE]
[TD][TABLE="width: 332"]
<colgroup><col></colgroup><tbody>[TR="class: grid"]
[TD]<--- = SERIESSUM(A6,4,-1,$F$2:$F$5)+$F$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR]
[TD="class: xl65, width: 135, align: right"]-0.0000078125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 420"]
<colgroup><col></colgroup><tbody>[TR]
[TD]<--- { =TRANSPOSE(LINEST(B2:B6,A2:A6^{1,2,3,4})) }
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]101
[/TD]
[TD][/TD]
[TD][TABLE="width: 135"]
<colgroup><col width="135"></colgroup><tbody>[TR="class: grid"]
[TD="width: 135"]-821.1016015625[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 332"]
<colgroup><col></colgroup><tbody>[TR="class: grid"]
[TD]<--- = SERIESSUM(A7,4,-1,$F$2:$F$5)+$F$6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Nil
[/TD]
[TD]Nil
[/TD]
[/TR]
</tbody>[/TABLE]


As you can tell, I haven't actually used the SERIESSUM and LINEST functions before so I am guessing that this is where I am getting tripped up. Sorry about this!

 

[Archer99]

Okay, Row 6 did something weird in the last post but you get the idea....