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...
You've obviously done this in Excel (or Google). Can you post the actual workbook (far more helpful than an image alone) on OneDrive or GoogleDrive, with a link pasted here that grants access.
I think I have worked it out how to do it. here is the link https://1drv.ms/x/s!Aux_4hjPdZM3j9dI4Jvt6gV-M2OSkg?e=DhRu7g
So the League Table worksheet is populated from the Gameweek 1 and once completed it will get the info from each of the gameweeks.
So basically in the gameweek sheet what I have is the matches for each player. If the scores match the results they will get '1' as a correct result or '0' if it does not match. The outcome on the results has been delivered according to the match result and is populated in the outcome column. If the outcome of the player matches then they will get '1' and if not they get '0' .At the bottom of each players results score there is a total.
Once all the results have been inputted manually then each players scores will be populated automatically.The total score is then populated onto the League table worksheet and the work sheet is one I have been using for sometime and it will populate the scores from the total of correct scores and outcomes.
One thing I am stuck on at the moment is ranking. In column C I have been able to use an automatic ranking system based on the highest to lowest score in the FC column but what I would love it to do is to move each row in line with the rank so it automatically juggles the table .
The way I sort the table manually is to show who is ranked in order by firstly largest to smallest of column FC then I sort in order secondly largest to smallest of column FB and then thirdly column FB.
After I have worked that out it would be great if I was able to get the players to put their own scores in so that all i need to do is to enter the results. If I do that I will need to be able to put a constraint on it so that after a certain date and time they are no longer able to edit it. I think I may need to do that web based but not sure how to get the both to communicate to each other.
Any help on that would be great.
So the League Table worksheet is populated from the Gameweek 1 and once completed it will get the info from each of the gameweeks.
So basically in the gameweek sheet what I have is the matches for each player. If the scores match the results they will get '1' as a correct result or '0' if it does not match. The outcome on the results has been delivered according to the match result and is populated in the outcome column. If the outcome of the player matches then they will get '1' and if not they get '0' .At the bottom of each players results score there is a total.
Once all the results have been inputted manually then each players scores will be populated automatically.The total score is then populated onto the League table worksheet and the work sheet is one I have been using for sometime and it will populate the scores from the total of correct scores and outcomes.
One thing I am stuck on at the moment is ranking. In column C I have been able to use an automatic ranking system based on the highest to lowest score in the FC column but what I would love it to do is to move each row in line with the rank so it automatically juggles the table .
The way I sort the table manually is to show who is ranked in order by firstly largest to smallest of column FC then I sort in order secondly largest to smallest of column FB and then thirdly column FB.
After I have worked that out it would be great if I was able to get the players to put their own scores in so that all i need to do is to enter the results. If I do that I will need to be able to put a constraint on it so that after a certain date and time they are no longer able to edit it. I think I may need to do that web based but not sure how to get the both to communicate to each other.
Any help on that would be great.
- I just wanted to drop in here and reiterate John (mathetes)'s point about having an master database that follows a single table format. There are a number of options I see for you but just imagine the "input table" as John mentioned might be:
Date, UserID, Week#, Team Name, Score, W-L-T
so this isn't as 'pretty' as the example above for Arsenal v Tottenham 2 1 would be spread across 2 (possible non-adjacent) lines like:
2023-06-02, Player1, 1, Arsenal, 2, W
2023-06-02, Player1, 1, Tottenham, 1, L
and then you can in the same table OR separate table have the results and you can 'label' them as UserID = RESULTS
So I'm sure you wonder why the heck I would want to make it look like that. and the answer is that then all weeks and all players have the same format and you can then use excel functions to the the work. ALSO, I can imagine you can use your online Excel to create a FORM for data entry. You can have the users enter the data and the Form will automatically populate that table. YOU could also use that same form under the RESULTS ID and enter the results.
Using a Form has the added benefit of preventing everyone from peeking at or even worse changing other's picks on a shared form. - Thank you, I have knowledge of conditional formatting although this is not what I am wanting to do with the table to be fair.
Thank you for your support it is much appreciated as it has inspired me to keep going lol. I'll take a look at all you've done when I have some time--it's a busier weekend than normal around my home. Glad you've figured things out.
When it comes to ranking, you might find some of the colored conditional formatting methods helpful. Here's one example, as it only goes from dark green through yellow, to red, in graduated shades, illustrating in this case the number of times each person (the vertical column in each case is a person) has gotten, over the course of time, a given score for daily competition
What I mean is, instead of sorting the names--although that's doable--highlight the lowest/highest scores by means of colors, but keep the names all in alphabetical order. That way a person always knows where they are on this list alphabetically, and can tell quickly where they stand in the ranking based on the color.
Here's the portion of the Conditional Formatting dialog box that applies.
