Forum Discussion
Excel program help for a prediction league
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/footpredleague
You'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.From the example I sent you of my Word Game tracking system, I wanted you to see that ALL of the raw data, for every player, it's ALL entered onto a single database. That's key, if you want to take advantage of Excel's abilities to parse and summarize data. It (Excel) works far more readily from a single database. That's the challenge to you. Not separate sheets for each player.
There can be a separate database for the actual results each week. But the predictions from all players for all games for the entire season--those should all be in one table, one sheet/tab.
So my questions are
- how will you enter each player's predictions so that they can be compared, by the computer, to actuals?
- how you will enter the actuals so they can be compared to the predictions?
For example, you know (somehow) which is "home" and which is "away" and so far as I can tell you collect that variable in the prediction end of things; but where (and how) is it stored in the actual, or is it? Might it not be easier for both to be in a format that is easy to compare, e.g.,
- prediction: A v B, 2-1
- actual: A v B, 3-2. or actual A v B, 2-3 or Actual 3-3 (i.e., draw is indicated by whatever the two equal scores were)
- always show the "Home" team name first in the pairings, with the scores of each team in the order of the names.
My basic point, it has to be in the data itself, how it is arranged, that can serve as the basis for the comparisons needed in order to recognize and score all the things that need to be noted. Right now it appears that you, when you do it manually, know whether a match is home or away for which of the teams that applies to. Something like that needs to be made explicit, rather than assumed, if it is to be accounted for by Excel.
These are the questions I'd be asking and exploring if we were sitting down face-to-face; it's harder to do via messages ... so I appreciate your patience.
P.S. You know what could be really helpful as a next step? Could you put together a mock Excel sheet representing the predictions of five players in your league, predictions for five or six different games, for two different weeks.
Then, on a separate sheet in the same workbook, the actual (fictitious) results for those five or six matches for the two weeks.
Organize the data in whatever format makes most sense to you. I might come back with suggestions or questions, but if you start with whatever makes sense to you, that would be a start.