Forum Discussion
Excel program help for a prediction league
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.)
- Thank you for your reply.
So I am not an expert but I see it that if I have one workbook/sheet where I have 38 weeks worth of matches on which there may be 15 matches in one week and maybe 6 in another week depending how the season goes from the F.A.
With each game week I could enter everyones prediction in manually on their own workbook/sheet for that week . Then this will be compared to a separate workbook/sheet that will have the master list of games played for each week which I will input the actual result of the matches. Then this is compared to everyones elses predictions and if they match then this will populate the table for each gameweek.
So I suppose if I took each gameweek as it comes with say an average of 10 matches played so it will be
A v B
C v D
E v F
G v H
I v J
K v L
M v N
O v P
Q v R
S v T
If this is put onto a master sheet for each game week then this will populate the same fixtures on each of the players workbook/sheet . On their own workbook/sheet I will enter each of the predictions manually ( easiest way for me to do it as its a facebook thing and I want all predictions to be put onto that and then I can take the predictions from that to go onto their workbook/sheet)
Once the matches have been played I will then update the score and outcome for each match which will then compare to each plyers workbook.sheet .
So if Player 1 predicts say the first week with A v B 2-1 a ( a =away win, H=Home win , D = draw ) This means that they predicted the score to be 2-1 but they predicted a different outcome as an away win . This way they have two bites of the cherry to get points. They could input 2-1 H which will mean if that is the correct score they will get 5 pints for the 2-1 and 2 points for the H home win. Or if they predicted 3-1 H then they would not get any points for the match result but would get 2 points for the correct outcome.
The master sheet will compare to their predictions and if they hit the correct score it will poulate the table as a correct score etc.
I hope that makes sense.
So in essence it feels like I need to have several workbooks/sheets for each player for each week and then the master sheet with each weeks matches and then get another workbook/sheet with with the actual table that will populate from all the other workbooks/sheets.
Not sure if I can explain that any more than that.
