Extrapolate Data

**erock24** · 03-27-2009, 05:17 PM

Can someone show me how to extrapolate data to the end of the month. I have supplied a file with actual data, I plotted the data and created a trendline. I think I need to use the LINEST function, but can't figure it out.
Thankyou.

**zbor** · 03-27-2009, 05:37 PM

You can write formula y = -2E-05x4 + 0,0013x3 - 0,0248x2 + 0,1341x + 3,9907 as =power(F4;4)*2*exp(-5)... etc

**erock24** · 03-27-2009, 05:52 PM

Could you give me an in cell example? I'm looking to forecast days 28,29,30,31.

Thank you.

**zbor** · 03-27-2009, 05:58 PM

=-2*power(10;-5)*power(day(e2);4)+0,0013*power(day(e2);3)-0,0248*power(day(e2);2)+0,1341*day(e2)+3,9907

**shg** · 03-27-2009, 08:55 PM

1. Select H1:H5 and array-enter =TRANSPOSE(LINEST(F2:F28, D2:D28 ^ {1,2,3,4} ) ) to see the parameters of the trendline (i.e., confirm with Ctrl+Shift+Enter)

2. Continue columns D and E down four more rows (to the 31st)

3. In F29 and copy down, =SERIESSUM(D29, 4, -1, $H$1:$H$4) + $H$5

**erock24** · 03-28-2009, 11:27 AM

Thankyou all for the replies.

Shg,
If I were to change the order of the polynomial, say to 5. Am I correct in assuming that I would need to adjust:
1. Select H1:H6 and array-enter =TRANSPOSE(LINEST(F2:F28, D2:D28 ^ {1,2,3,4,5} ) )

3. In F29 and copy down, =SERIESSUM(D29, 5, -1, $H$1:$H$5) + $H$6.

Thankyou.

**shg** · 03-28-2009, 12:23 PM

You are correct.

That said, I think most garden-variety data isn't polynomial in behavior, and especially odd-order polynomial. Sure you're using the right type of function?

**erock24** · 03-30-2009, 10:28 AM

Well, to be honest, no..but I'm working on that. Would you know of any links that could provide some info on the different types of functions? I'm also trying to lean the different types of forecasting one can do in excel. Would you know how to apply an exponential moving average to my data set to forecast the other days?

Thank you for your time and help.

**shg** · 03-30-2009, 11:21 AM

An exponential moving average (EMA) over N periods is, I believe, computed as follows:

1. The first EMA appears in period N, and is the average price over the N periods.

2. For all subsequent EMAs (starting with period N+1)

EMA(k+1) = P * price + (1-P) * EMA(k)

where P = 2 / (N+1)