Use Polynomial Equation to Forecast x values for Known y Values

[kalikj]

Dear All, please can anyone help me with this question.

I have a series of 5 known y values and five known x values. I have used the Excel in-built charting facility to plot a line graph / curve for these values, and have added a polynomial trend line projecting 6 further y values for a further series of known x's. I have enabled the chart option for the polynomial equation to be shown on the chart, which gives me the following:

y = 2.75x2 - 5.17x + 2.56.

The x and y values I am using are as follows:

[TABLE="width: 147"]
<tbody>[TR]
[TD]x[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Metres[/TD]
[TD]Drop (cms)[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD="align: right"]11.8[/TD]
[/TR]
[TR]
[TD="align: right"]250[/TD]
[TD="align: right"]25.6[/TD]
[/TR]
[TR]
[TD="align: right"]300[/TD]
[TD="align: right"]45.6[/TD]
[/TR]
[TR]
[TD="align: right"]350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]450[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]550[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]600[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My issue is that when I use the equation to try to predict the next unknown y value by substituting x with the next actual x value (350) for which I am trying to forecast the y value, I get a nonsensical answer. i.e. I am computing y = ((2.75*(3502)) - (5.17*350)) + 2.56.

Please can anyone tell me why I do not get a sensible answer with this approach?

On a related question, I have tried to get Excel to show me the data for the projected y values by including a table underneath the line chart; however it only gives me the values for the known points and not the projected ones. Is there any way to choose an option so that Excel will show the data it is uisng to create the trendline plot?

Thank you in advance for your help. ​

 

[jkpieterse]

The correct formula would be:

((2.75*(350^2)) - (5.17*350)) + 2.56

or, simplified:

2.75*350*350 - 5.17*350 + 2.56

 

[kalikj]

Dear All, please can anyone help me with this question.

I have a series of 5 known y values and five known x values. I have used the Excel in-built charting facility to plot a line graph / curve for these values, and have added a polynomial trend line projecting 6 further y values for a further series of known x's. I have enabled the chart option for the polynomial equation to be shown on the chart, which gives me the following:

y = 2.75x2 - 5.17x + 2.56.

The x and y values I am using are as follows:

[TABLE="width: 147"]
<tbody>[TR]
[TD]x[/TD]
[TD]y[/TD]
[/TR]
[TR]
[TD]Metres[/TD]
[TD]Drop (cms)[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD="align: right"]11.8[/TD]
[/TR]
[TR]
[TD="align: right"]250[/TD]
[TD="align: right"]25.6[/TD]
[/TR]
[TR]
[TD="align: right"]300[/TD]
[TD="align: right"]45.6[/TD]
[/TR]
[TR]
[TD="align: right"]350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]450[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]550[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]600[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My issue is that when I use the equation to try to predict the next unknown y value by substituting x with the next actual x value (350) for which I am trying to forecast the y value, I get a nonsensical answer. i.e. I am computing y = ((2.75*(3502)) - (5.17*350)) + 2.56.

Please can anyone tell me why I do not get a sensible answer with this approach?

On a related question, I have tried to get Excel to show me the data for the projected y values by including a table underneath the line chart; however it only gives me the values for the known points and not the projected ones. Is there any way to choose an option so that Excel will show the data it is uisng to create the trendline plot?

Thank you in advance for your help. ​

Dear Jan,

Thank you for your response. Your suggested solutions give a value of 335,068, which is the same result I was getting when substituting 350 as the x value for this equation. The issue is that value should be in the region of 70 to 80, which is the approximate value shown on the trendline plot (i.e. the polynomial trendline plotted by Excel giving the stated formula).

Do you have any ideas as to why the equation, supposedly underpinning the polynomial trendline is not giving the result that has actually been plotted and is predicting a value that is so massively disconnected?

Best regards,

Jason

 

[kalikj]

Dear Jan,

Thank you for your response. Your suggested solutions give a value of 335,068, which is the same result I was getting when substituting 350 as the x value for this equation. The issue is that value should be in the region of 70 to 80, which is the approximate value shown on the trendline plot (i.e. the polynomial trendline plotted by Excel giving the stated formula).

Do you have any ideas as to why the equation, supposedly underpinning the polynomial trendline is not giving the result that has actually been plotted and is predicting a value that is so massively disconnected?

Best regards,

Jason

 

[kalikj]

SOLVED: Use Polynomial Equation to Forecast x values for Known y Values

PROBLEM SOLVED: thought I would publish the solution as this can hopefully help others with non linear forecasting problems.

Thank you once again for your help Jan. I managed to work up a solution by gaining some input from my daughters friends studying Maths at Bristol University in the UK. They gave me the equation for a second order polynomial equation. I was able to use in built Excel functions in order to calculate the coefficients required to solve the equation, then enter the equation to reference these coefficients plus the further "y's" for which I wanted to forecast the new x values.

The equation for a 2nd order polynomial equation is: y = (c2 * x^2) + (c1 * x ^1) + b

To calculate the coefficients required to apply this equation (c2, c1 & b), use the following Excel formulae:

c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)

y and x in these formulae refer to two separate name ranges that you will need to create (referring to the cell ranges featuring the known y's and known x's).

I have not worked out how to attach a workbook to my post. If that is allowed within this forum and someone can tell me how to do it, I can upload a sample file applying the solution.

 

[joeu2004]

I have a series of 5 known y values and five known x values. I have used the Excel in-built charting facility to plot a line graph / curve for these values, and have added a polynomial trend line projecting 6 further y values for a further series of known x's. I have enabled the chart option for the polynomial equation to be shown on the chart, which gives me the following: y = 2.75x2 - 5.17x + 2.56.
[....] My issue is that when I use the equation to try to predict the next unknown y value by substituting x with the next actual x value (350) for which I am trying to forecast the y value, I get a nonsensical answer.

You are making several mistakes.

First, you are using a Line chart. The trendline in a Line chart uses x={1,2,3,4,5}, not your x values {100,150,200,250,300}. That is why the polynomial formula gives such different y-est values when you use the correct formula, to wit: =2.75*A2^2 - 5.17*A2 + 2.56, where A2:A6 contain your x values.

Instead, you should use an XY Scatter chart. Then the 2nd-order polynomial will display the formula y = 0.0011*x^2 - 0.2134*x + 10.48, with R^2 = 0.9999. When you use those coefficients in the correct formula, to wit: =0.0011*A2^2 - 0.2134*A2 + 10.48, the y-est values are about {0.14, 3.22, 11.8, 25.88, 45.46}, which are close to the given y-values {0, 3.5, 11.8, 25.6, 45.6}.

Second, if you use the coefficients displayed for the chart trendline, you should format the trendline label to display many more decimal places. I prefer to use the Scientific format with 14 decimal places. That displays 15 significant digits for every coefficient, which is the most that Excel formats, an arbitrary limitation, but more than sufficient for this purpose.

However, it appears that you later chose to use LINEST in Excel to generate the coefficients. That is preferable; and the result will be precise to at least 15 significant digits.

Third, in general, a polynomial trendline should only be used to interpolate between the given x values. Unless the polynomial truly represents the relationship between the given x and y values, polynomial formulas tend to "blow up" as we extrapolate beyond the given x values. By "blow up", I mean: they are no longer a good estimator of y values because the polynomial curve deviates significantly from the curve for the given x values, turning sharp up or down.

To demonstrate, extend the chart trendline several units forward and backward.

It is usually better to use linear, exponential, log and power trendlines, even if R^2 is less than a near-perfect fit. But note that x=0 cannot be a data point for some of those trendlines.

 

[kalikj]

Dear JoeU,

Great input , thank you. I have noted your points and will apply when fitting curves, both now and in the future.

Thank you once again.

Best regards,

Jason

 

[joeu2004]

PS....

you should use an XY Scatter chart. Then the 2nd-order polynomial will display the formula y = 0.0011*x^2 - 0.2134*x + 10.48, with R^2 = 0.9999. [....] if you use the coefficients displayed for the chart trendline, you should format the trendline label to display many more decimal places. I prefer to use the Scientific format with 14 decimal places
[....] it appears that you later chose to use LINEST in Excel to generate the coefficients. That is preferable; and the result will be precise to at least 15 significant digits.

Note that sometimes, LINEST returns very different coefficients, especially for higher-order polynomials. Presumably, the reason is: the chart trendline and LINEST use different algorithms internally. In particular, the inputs to LINEST are arrays of powers of x, which sometimes exceed the limits of accurate binary representation. The chart trendline can be smarter in its calculations.

The differences are acceptable as long as both sets of coefficients generate reasonably close, albeit possibly different sets of y-est values.

However, sometimes LINEST returns bogus coefficients, again because its inputs exceed the limits of accurate binary representation. So it is prudent to scrutinize the LINEST result, comparing the y-est and original y values. If the LINEST coefficients do not work, usually it is better to copy the trendline coefficients, formatted to display 15 significant digits.