Excels FORECAST/LINEST function

Grumpy_Old_Duffer

I've never used FORECAST or LINEST before and am having problems getting a sensible prediction. I don't think I'm using either function correctly as I've tried FORECAST(A2:10) & LINEST (A2:10) in cell C11 and copied the formula down.
Column (B) is Actual Odds recorded from a website in 1 second intervals.

As an example, how or what could I use (if not the FORECAST or LINEST function) to predict the Odds at R. 20, and show the result in Column (C).

(A) .... (B) ....... (C)
R. .... Odds
1 ......3.00
2 ......3.00
3 ......3.00
4 ......3.10
5 ......3.05
6 ......3.00
7 ......3.10
8 ......3.15
9 ......3.15
10 .....3.20
11 .....3.25
12 .....3.25
13 .....3.20
14 .....3.25
15
16
17
18
19
20

Wombat

Grumpy - do you mean a prediction based on the average change in odds per minute since the first odds downloaded?

Grumpy_Old_Duffer

Wombat,

My thoughts were to have predicted Odds in Col. C, for the next 15 to 20 entries that are yet to be filled in Col. B ..... bearing-in-mind each entry in Col. B is in 1 second intervals.

Wombat

So, say for example only the first 2 odds amounts were in, you'd expect the predicted odds in column C for the next 20 seconds to be all 3.00 since the odds had previously stayed steady at 3.00?

In other words, you want a formula which will take the first reading of the odds (3.00 in this case), the latest odds reading (3.25) and predict the rate of change in the odds based on the average change in odds per second?

In this case it would be (3.25 - 3.00)/14 = 0.017
So C15 would = 3.25 + 0.017
C16 would = 3.25 + (2 x 0.017)
C17 would = 3.25 + (3 x 0.017) etc.

These values to be recalculated every time the odds are read?

Wombat

I haven't used Forecast before, but I think it's possible to use this.

Forecast (x, known_y's, known_x's)

Where I would think x = current second number, known_y's is the list of odds readings from the website, and known_x's is the no. of seconds since starting to take readings of each reading.

Known_x's and Known_y's may be the other way round - not sure. You may have to play around with that unless someone else knows?

So in col C there would be a series of formulas similar to:

C3=Forecast(2, B2:B3, A2: A3)
C4=Forecast(3, B2:B4, A2: A4)

(Spaces inserted to prevent smiley!!!!)

etc.

I'm in unknown territory myself here, so if it works....great! If it doesn't I hope someone who knows posts the answer!!!

Good luck Grumpy.

Grumpy_Old_Duffer

Wombat wrote:

So, say for example only the first 2 odds amounts were in, you'd expect the predicted odds in column C for the next 20 seconds to be all 3.00 since the odds had previously stayed steady at 3.00?

In other words, you want a formula which will take the first reading of the odds (3.00 in this case), the latest odds reading (3.25) and predict the rate of change in the odds based on the average change in odds per second?

In this case it would be (3.25 - 3.00)/14 = 0.017
So C15 would = 3.25 + 0.017
C16 would = 3.25 + (2 x 0.017)
C17 would = 3.25 + (3 x 0.017) etc.

These values to be recalculated every time the odds are read?

No, I wouldn't expect a prediction until at least 15 to 20 odds were entered from the download. and the problem with the above is that it wouldn't pick-up the change of direction in Odds, e.g. after 100 seconds (cell C100), based on the above theory, the predicted odds would be about 3.25+(86x0.017)=4.71, i.e. a one-way movement and probably impractable .... imagine what the odds would be after 600 seconds!!!

Wombat

Yep, I see that, Grumpy. What about the Forecast suggestion? Did you try that?

I think the problem with the way you were doing it was that the parameters were incomplete. You need 3 parameters:

FORECAST

Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

Syntax

FORECAST(x,known_y's,known_x's)

X is the data point for which you want to predict a value.

Known_y's is the dependent array or range of data.

Known_x's is the independent array or range of data.



Remarks

• If x is nonnumeric, FORECAST returns the #VALUE! error value.
• If known_y's and known_x's are empty or contain a different number of data points, FORECAST returns the #N/A error value.
• If the variance of known_x's equals zero, then FORECAST returns the #DIV/0! error value.

Here are a couple of examples:

http://exceltips.vitalnews.com/Pages/T0590_Using_the_FORECAST_Function.html

http://www.utexas.edu/its/training/handouts/excelstat/