Anyone know how to setup the forecast function so it can give me a prediction value
Ex. Results
W
L
L
D
W
L
D
--->>forecast function to calculate past results
Any help
Anyone know how to setup the forecast function so it can give me a prediction value
Ex. Results
W
L
L
D
W
L
D
--->>forecast function to calculate past results
Any help
AS explained in the help file https://support.office.com/en-us/art...4-7ad38bbeda99 the model used by the forecast function is a simple linear (y=mx+b) type model. If your historic W/L/D data can be fit to a straight line and extrapolated, then you can use the FORECAST() function. However, most W/L/D data will not really fit a linear model, so I doubt you will get the FORECAST() function to do this.
I do not do this kind of programming, but it seems that most algorithms that I come across for this kind of prediction are based on some kind of rating system (like Elo ratings are used for chess tournaments https://en.wikipedia.org/wiki/Elo_rating_system ). I would expect a program/spreadsheet that predicts future competition would go through these steps:
1) Use historical data to determine a "rating" for each competitor/team.
2) From these "ratings", use a Monte Carlo type algorithm to "play" the next match(es) several times.
3) From the results of the Monte Carlo simulations, see if there are any patterns that emerge regarding Wins, draws, and losses for the "played" matches.
Originally Posted by shg
Basically to get a prediction factor at a season start (NHL for example) google a couple of past seasons data and paste into Excel
Assign a value for Home Wins, AW, AD, and Home Draws. A home win could be 2*2.5 and an away win would be 2*3.5 for example, similar for the draws. I also assign values for # of home goals and a larger value for away goals. In essence the more data you can assign a value to, the better the prediction, values can be added to win streaks, deducted for losing streaks, etc, etc.
After assigning the values, add the teams score. Do this for all teams and all past seasons data. This data is called Club Form.
Now you have a league table based on past seasons.
Use the Rank formula to find the top & bottom teams, for example the top team will be 1 and the bottom team will be 24.
Create a league table for the current season, and enter the results after each game, using the same formulas from above, assign win, loss, & tie values. Add these to the past seasons club form, and use RANK again to create ongoing updates as the season progresses.
After entering the fixture list for the new season into excel, we will say this takes up Col A,B,C, D,then use IF formulas to match the home team in Col E, and the away team in Col F. In two separate columns use INDEX MATCH for the home team and away team to match the teams rank number.
In another column you can now use further IF statements to determine the game prediction (this is only a win,tie or loss situation - not goals).
The lower the number the better the chance of beating the opposing team. eg: Rangers are 1 the Maple Laffs are 24. Who's going to win that one ??? The difficulty is predicting the closer teams: 10 against 11, 22 against 23 for example. Does the Home team usually win at home or does the away team ? If ranking points are tied lean towards the home team within your IF statements.
Now having said all that, here is a worksheet I use within my EPL workbook that has the ranking formulas.
Good luck.
Last edited by BlindAlley; 09-16-2016 at 09:37 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks