Hopefully I can explain this well enough for someone to help me. I track stats for a basketball team, and am looking to sum up the total minutes every particular lineup of players is on the court.
This data would be on a new worksheet as since we have 12 players on the team, if I calculate it correctly there would be possible of 792 lineup combinations. There is an individual sheet for each game (23 total games/sheets), so the data would be pulling from multiple sheets.
The way the lineups and minutes are laid out as such on the game sheets. Each time a player subs into the game, a new row is created.
<tbody>
</tbody>
Above would be in one game and that lineup would have played together for a total of 4 minutes and 2 seconds. So for the aggregate sheet, I would want to see the total minutes that the line up of 3, 12, 31, 33, 45 played together for the entire season. Where I am really struggling is how to do this is like above where row sub1 is 3,12, 31,33, 45, but row sub4 is in a different order of 3,33,31,12,45. That is still the same lineup, so I would need those values to be added together, not two separate lineups.
We also calculate Plus/Minus stats for when each lineup is on the court whether they scored + or - points compared to the other team during that time frame. I really want this sheet to use which lineups are our best scoring lineups over the course of the season. Once this lineups / minutes part is figured out, I can use the same formula to bring in the other stats I need.
Any help is appreciated.
Thanks,
This data would be on a new worksheet as since we have 12 players on the team, if I calculate it correctly there would be possible of 792 lineup combinations. There is an individual sheet for each game (23 total games/sheets), so the data would be pulling from multiple sheets.
The way the lineups and minutes are laid out as such on the game sheets. Each time a player subs into the game, a new row is created.
Player # | Player # | Player # | Player # | Player # | Minutes | |
sub1 | 3 | 12 | 31 | 33 | 45 | 3:00 |
sub2 | 3 | 15 | 31 | 33 | 45 | 4:30 |
sub3 | 3 | 15 | 42 | 20 | 45 | 2:07 |
sub4 | 3 | 33 | 31 | 12 | 45 | 1:02 |
<tbody>
</tbody>
Above would be in one game and that lineup would have played together for a total of 4 minutes and 2 seconds. So for the aggregate sheet, I would want to see the total minutes that the line up of 3, 12, 31, 33, 45 played together for the entire season. Where I am really struggling is how to do this is like above where row sub1 is 3,12, 31,33, 45, but row sub4 is in a different order of 3,33,31,12,45. That is still the same lineup, so I would need those values to be added together, not two separate lineups.
We also calculate Plus/Minus stats for when each lineup is on the court whether they scored + or - points compared to the other team during that time frame. I really want this sheet to use which lineups are our best scoring lineups over the course of the season. Once this lineups / minutes part is figured out, I can use the same formula to bring in the other stats I need.
Any help is appreciated.
Thanks,