How to forecast points using LINEST

[bogdanavic]

This is probably a really easy one to solve so hoping some kind soul can help me out!

I have a simple two variable data set which has a polyominal relationship and from which I've plotted out a curve based on the X,Y variables I've been provided.
It's very simple and here is a sample of that data:

X Axis Y Axis
9.67 8.6%
26.83 21.1%
43.64 28.7%
60.20 34.2%
74.62 38.1%
89.97 41.3%
107.30 44.5%
124.15 47.2%

I then have another sample set of X axis points which increases in increments of 5 as per below

Sample X axis
5
10
15
20
25
30
35
40

What I need to do is establish the relationship from my actual (real) data and apply it to the sample X axis data so that it projects a well estimated Y axis forecast. The chart trend line I've created is a 6 order polyominal and I've had no success copying that out and using it. I've therefore tried to use LINEST as I understand this is a much better way to go but have also come unstuck (also the fact that I'm using a mac seems to make array function harder too!)

I'm sure this is very easy to resolve and hoping someone kind can help me out

[MrShorty]

I'm not sure what you have tried. I would have expected something like:

1) Enter LINEST() function. Select an array of 7 horizontal cells and array enter your LINEST() function. I'm not sure what key sequence that is on the MAC, but I get the impression that it is readily doable. A quick internet search suggests that it is ctrl-shift-enter just like in windows. I see some reference to cmd-enter or other sequences for older versions of excel.
2) In the column adjacent to your new x values, enter your 6th order polynomial (SERIESSUM() is useful for this, or write out the polynomial). With the right combination of relative and absolute references, copy the formula down as far as needed.

[joeu2004]

(Note: I use Windows on a PC, not a Mac. I hope you can make use of the following.)

It would help to know what polynomial you used. [ERRATA: I mised the part of the OP where you clearly say: ``The chart trend line I've created is a 6 order polyominal``. Klunk!]

The standard Excel polynomial trendline in charts is limited to an order-6 polynomial.

But with LINEST, we are not so limited. And an order-7 polynomial fits the known data of 8 exactly (subject to binary arithmetic anomalies).

In the attached Excel file, selected D2:K4 and array-entered (press ctrl+shift+Enter instead of just Enter) the following formula:

=LINEST(B2:B9,A2:A9^{1,2,3,4,5,6,7},TRUE,TRUE)

The advantage of the "TRUE,TRUE" arguments is that calculates the R^2 (measure of fitness) in D4. As we expect, it is exactly 1.0.

In order to calculate est-y for x=5,10,15,etc, enter the following formula into B12 and copy into B13:B19:

=SERIESSUM(A12,7,-1,$D$2:$J$2)+$K$2

In this particular case, we could write simply =SERIESSUM(A12,7,-1,$D$2:$K$2). But it is a "good practice" to add the x^0 coefficient separately, just in case it is zero.

-----

Some comments....

I usually deprecate the use of a polynomial trendline, especially a high-order polynomial, unless the data was actually generated by such a polynomial.

Although a polynomial trend is usually okay for interpolation, usually it is not good for extrapolating beyond the given data because such formuals tend to "blow up" (wildly stray from expectations).

But I must admit: in this particular case, the order-7 polynomial calculates a reasonable est-y for x=5.

And a natural log (ln) trendline (which would be my choice) does not fit the data and extrapolate nearly as well.

-----

A note on the attachements....

I have included an "xls" file because your profile says that you use Excel 2003. (Correct?)

But in this forum, we cannot open the file directly in Excel because the forum appends an "xlsx" extension.

Instead, save the file into a folder. It will be saved with the "xls" extension. Then you can open the file in Excel.

[bogdanavic]

Great thanks for this. I will give it a go!

[joeu2004]

The chart trend line I've created is a 6 order polyominal and I've had no success copying that out and using it. I've therefore tried to use LINEST

Sorry, I overlooked this explanation. IMHO, LINEST is the better way to go. I demonstrated its use for order-7 polynomials. Hopefully, you can make the adjustments if you prefer order-6.

Sometimes, LINEST produces different coefficients -- sometimes only infinitesimally different, but sometimes significantly different. Not a problem, so long as the resulting polynomial effectively produces est-y values.

However, sometimes LINEST produces bogus coefficients, or it fails entirely. In that case, and when we are just lazy or we don't remember how to use LINEST for the particular trendline, it might be helpful to know how to copy the trendline coefficients.

If necessary, right-click the trendline, click Format Trendline, and click next to "Display equation" to put a checkmark next to it. (I like to do the same with "Display R-squared".)

Then right-click the trendline label (the equation in the chart), click Format Trendline Label > Number > Scientific and enter 14 in the "Decimal places" field, then click Close.

You might not understand the Scientific format. But with 14 dp, that displays all coefficients with the maximum 15 signficiant digits, regardless of their magnitudes.

Hope that is helpful for the future.

Copy the text of the equation (just the right-hand side) and paste into the horizontal cells (one more than the polynomial order) that will become the individual coefficients.

Finally, edit each cell, reducing the text to the appropriate coefficient, with the left-most coefficient in the left-most cell. Note that if the appropriate part of the equation is "- 6.19843074953529E-08...", you need to include the subtraction operator (minus) with the constant (i.e. -6.19843074953529E-08, not 6.19843074953529E-08).