using functions trend and forecast

Posted by Rudi Holans on September 11, 2001 8:18 AM

Hello,

If you have a range of data (like 1 2 3 4 ), i need a function that predicts the next value. In this particular case it should be 5, knowing that the trend is always +1.

But what do you do when you have a complete different range of values like:
123
152
142
195
165

I need a function that predicts the next value based upon that data available in the existing range.
Should it work with trend or forecast?

Please reply urgently.

Kind regards,

Rudi Holans
CG Distribution

Posted by Aladin Akyurek on September 11, 2001 10:17 PM

Rudi,

I doubt that FORECAST is applicable to your example (see its syntax for what is required).

I assume that the example numbers represent the behavior of some object in some domain. However, if the linearity assumption holds for the object in question, TREND can be used to predict next value or values.

Lets say these values are in the range A1:A5.

Activate A1.
Activate Insert|Name|Define.
Enter DATA in the Names in Workbook box.
Enter the following formula in the Refers To box.

=OFFSET(x!$A$2,0,0,COUNTA(x!$A:$A),1)

Click OK.

In B1 enter: =TREND(DATA,,ROW(INDIRECT(COUNT(DATA)+1&":"&COUNT(DATA)+1)))

This will predict the next value.

In C1 enter: =TREND(DATA,,ROW(INDIRECT(COUNT(DATA)+1&":"&COUNT(DATA)+4)))

Now select C1:C4 and hit CONTROL+SHIFT+ENTER at the same time.

You'll get 4 predicted values.

If you add more data (numbers) to column A, you'll see the above formulas to take them immeddiately into account in predicting the desired number of next values.

Aladin

===============