Forum Discussion
Excel program help for a prediction league
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.)
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.
mathetes I have attached a picture below as I am trying to look at it in steps.
So on the left hand side is 10 matches to be played and on the right hand side is the actual results.
What i want to be able to do in column l is to have a points system based on the score and outcome of the matches. I have put in some scores for player 1 with the actual scores for the matches and highlighted what the points should be for each game. I tried this but cannot get it to work for all outcomes as I think I have confused the formula=IF(COUNT(B3,C3,H3,J3)=4,IF(AND(B3=H3,C3=J3),3,IF(SIGN(B3-C3)=SIGN(H3-J3),1,0)),"")
Any help would be great.