Im a coach for a football team. Every day we train we plan our session in a numbers sheet. Same sheet every day. Now, instead of printing it and keeping the paper, I would like to record some statistics in the document for the remaining season. Every training we have some sort of a game and we always have a winning team. I would like to track how many times players are on the winning team during a season, without having to type it down every single time. With 5 sessions a week, for almost a full year, its a lot of players and sessions to keep track with!
I have a sheet which I duplicate every day. What I do now is a form with teams and names, and if that team comes out as a winner or not.
It looks something like this:
Team 1 Team 2
Jack Ross
Joe Chandler
John B Jimmy
John H George
Peter Sammy
Win Loss (these are pulled from a drop down menu)
Sometimes we can have 3 or 4 teams as well, but Im only interested in who’s on the winning team on that day, to see which players more often are on the winning team.
How do I get that info from all my sheets in to something that’s manageable and up to date after every single training session? I would also like to add the session date, to sum who’s performing best in a week, month or year. That info is of course in another table in the sheet.
In the longer run I would also like to extract more info from other tables in the sheet. Im thinking like what pitch we train on. How many players on the team that day. Do some players only win when we play small sided with fewer players, or the other way around. Maybe even see if some players win more often when they play with each other. All this info is in the sheet in different tables, but again, how could I extract this and make it searchable from my criterias in a functional way?
—–
Thomas
What you are trying to do is to create a database of information. To do that, one "record" of the database needs to be in a single row of a table. There are usually many ways to design this.
For instance, you could have a table that assigns players to teams. It would look like this:
Game (Date), Team Name, Game + Team (Hidden), Player Name, Outcome
Then you can have another table with wins and losses:
Game (Date), Team Name, Game + Team (Hidden), Outcome
In table 1, the column "Game + Team" uses a function to combine the game name and the team name. The exact same thing in table 2. Then in table 1 a LOOKUP function is used to get the Win or Loss from table 1 where the Game + Team matches.
So then each day you add the players in table 1, and the game results in table 2.
Then you can have table 3 that uses COUNTIFS to check table 1 for multiple conditions, like the player name, and "Win" or "Loss." Add more conditions for a start date (the game name is >= "6/1/2021") and end date so you can calculate your statistics. Instead of hardcoding the dates into those formulas, have them refer to a small table with just the start and end dates in them.