Forum Discussion
Excel program help for a prediction league
Hi All, I have been running a football prediction league for the past 15 years and I have had to check everyones weekly predictions manually and then inputting onto an excel spread sheet to then form a table.So how it works is that I put onto facebook all the matches for that gameweek that the members can predict the score and the outcome on abd then they just add the scores to their own reply. With the replies I then compare them to the actual results and outcomes of the matches and manually input that data onto the spread sheet.
What I would like to be able to do is to be able to input the matches onto the worksheet so that I can update the table much more easierly. SO for instance if I have 10 members predicting I can then add the fixtures for each member with their predictions and as the scores come in I can add the up to date scores into another part of the workbook so that it will update the main table as the scores change .
I have attached hopefully a complete years spreadsheet from a previous year>
I hope that makes sense.
12 Replies
So I don't know if I speak for others, but here's my dilemma: I'd love to be able to help you and suspect that Excel could be adapted quite readily to accomplish the task you have in front of you. HOWEVER, while I do know how to use Excel to track scores in games and compare results of various people, I haven't the slightest understanding of how a football prediction league would work, what the numbers on your sample spreadsheet mean, and how to make sense of them.
This may not apply to others among the Excel experts who frequent this site, but I know for me, you're going to have to explain the thinking process here, how predictions and actual results come together to produce a weekly result, etc., etc.
It's possible that the attached will help you to consider a different way of tracking and summarizing. This is a spreadsheet I've developed to track results of a group of four friends who daily engage in the recently popular word games (Wordle, Quordle, and three versions of Octordle). I enter the scores of each person in the second tab of the workbook; the front tab is a "Dashboard" that presents several different summaries of that data.
The key to my way of doing this is the database, a daily record, and that database (for the input of raw data) is separate from the output, the compilation of daily or long term results. All of that compilation takes place on the Dashboard sheet. I would recommend thinking about doing your tracking by means of at least two different areas or sheets, one each for Input and Output.
Thank you for getting back to me.
Just a bit more insight to what I am trying to achieve.
What the scoring system is that if someone predicts the correct SCORE it equates to 5 points. If the predict the correct OUTCOME they will get 2 points. So on the spread sheet if they had
So basically I am look at the English Premier League where there are 38 game weeks which contain 8 to 15 matches every game week.
I have 12 members that are playing and each member predicts the score of each match played and also the outcome e.g. if Arsenal were playing Tottenham and player 1 said 2-1 d what that means is that the player is predicting the match result to be a 2-1 win for Arsenal and will score that player 5 points . If he predicted the out come as H he would have got an extra 2 points . H= Home team win A = away team win and D means that it is a draw.
So each member will predict on all the matches played in each week and can score a maximum of 7 points per game played and as I say there can be up to 15 different games played each game week.
So what I am looking for is to be able to insert the score and outcomes for every player for every match and if they match the actual result of the match the the points score would populate on the table that I showed you in my initial post.
I think what I will need to do is create a drop down box so that I can put the name of each player in and then I would need to have 38 different worksheets for each game week . When I open up the game week worksheet i can then see that there are say 10 matches to predict on. When I choose another name on the drop down box it will come up with the match list without the scores until they enter the scores and out come .
This will need to be attached to another workbook I suspect so that I can put each game week match on it that will have the actual scores... once I enter the result of the match it will match it against each person on each game week being played and then total up the scores that will populate onto the main table.
I hope this is allowed as I have a link to my facebook group with all the games on it https://www.facebook.com/groups/footpredleagueYou've described fairly completely the Process that results in the points each player gets. So that's a good start.
What would be helpful now is to get a comprehensive understanding of the raw data Input, i.e., the data that will go through that Process in order to result in the desired Output.
At the risk of over-simplifying, I would describe the spreadsheet you shared with your first posting as one that combines pieces of each of the above--Input, Process, Output--into one place, but the reality is that you manually do a good deal of the process and just use Excel to calculate a running total.
To use Excel more fully, it helps to separate the Input so it's altogether on its own, even on its own sheet or tab in the workbook; similarly the Output--on its own page or tab--with the Process being that which converts the Input into the Output, the formulas and functions of Excel.
From what I can piece together reading your descriptions so far (and forgive any misunderstanding; as noted, you're dealing with an activity I know next to nothing about),
The raw data input from each of your players would be something like
- Week 1, John, Team A will beat Team B by 3-2
- Week 1, John, Team D will beat Team C by 5-0
- Week 1, John, Team F and Team E will Draw.....
- Week 1, John, (etc., following pattern above for each match of the week)
- Week 1, Tom, (etc., following pattern above for each match of the week)
- and so on for each of your players
Then there would be raw input after the weeks games have been played, something you'd enter, which would show the actual results for each game. Something like:
- Week 1, Team A lost to B by ____
- Week 1, Team C beat D by _____
- Week 1, Team E.... etc
What I've shown above is not probably the most efficient way to record that data, but it gets at the notion that it's just raw data. Facts (or Predictions of Facts). So far, no formulas are needed...your players are just providing their predictions and you are entering actual results.
It's only after all that Input is gathered together that we start using Excel to Process the raw data, to give each player the scores his/her predictions warrant, after which we can look at the desired display of the Output.
What I'd like from you before we get to the process is how you see that raw data most effectively being entered.
(By the way, a related question, do you see people entering their own data into a shared spreadsheet, either a shared workbook in Excel on OneDrive, or something comparable? That would certainly make your job easier....you'd just enter the actuals at the end of each week's games.)
