+ Reply to Thread
Results 1 to 3 of 3

Forecasting Wins, Draws, Loss

  1. #1
    Registered User
    Join Date
    09-04-2016
    Location
    Toronto
    MS-Off Ver
    97
    Posts
    5

    Forecasting Wins, Draws, Loss

    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

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,250

    Re: Forecasting Wins, Draws, Loss

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    500

    Re: Forecasting Wins, Draws, Loss

    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.
    Attached Files Attached Files
    Last edited by BlindAlley; 09-16-2016 at 09:37 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-09-2015, 03:20 AM
  2. [SOLVED] Trading Spreadsheet - Random win/loss outcome against established Win/Loss %
    By cruze2005 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-18-2014, 05:08 AM
  3. Tournament Draws
    By poolplayer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2014, 09:22 AM
  4. [SOLVED] Consecutive win/loss and current win/loss streak
    By TK2013 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-18-2013, 09:15 AM
  5. Pool Draws (bracket)
    By auststan69 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2006, 02:20 AM
  6. [SOLVED] Weight loss line chart to monitor weight loss progress
    By S Fox in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-08-2005, 01:15 PM
  7. [SOLVED] Macro that draws a chart
    By Susanne in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-18-2005, 08:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1