Excel Challenge: Equal Playing Time

My sister called me up today and said, I have a math question for you!

She is coaching her 5-year old son's basketball team. One of the requirements is that they give the kids equal playing time throughout each game.

Excel Challenge Equal Playing Time - Excel Campus

Here are the numbers:

  • There are 12 players on the team.
  • There are 6 games in the season.
  • There are 9 periods per game. Yes, nine. That's not a typo.
  • 5 players play in each period.

There are no substitutions during a period. The substitutions happen at the end of each period where some players are taken out of the game and others are put in.

Jump to Results Section

Video Explanation

Watch on YouTube & Subscribe to our Channel

Results Video

Watch on YouTube & Subscribe to our Channel

The Challenge

The challenge is to create a spreadsheet that shows a list of players and which periods they will play in during the game.

Again, only 5 players play in each period. Each of the 12 players should get equal playing time throughout the game. Or as close to equal as possible.

The layout of the spreadsheet (roster) is up to you. You can design it in a way that will be easy for the coach to read and manage from.

You can use the same roster for each game, or as a bonus, you can mix it up if that helps make the playing time more equal throughout the overall 6-game season.

The Rules

There are no rules or limitations in terms of the Excel features and tools you can use to generate the results.

It would be nice to have some flexibility in the parameters. For example, what if there are only 10 players on a team or 4 periods in a game?

How to Submit Your Answer

Please leave a comment below the post with a brief explanation of how you solved the problem. You can also post a share link to an Excel file that is stored on OneDrive, Google Drive, Dropbox, etc.

Or, you can upload a file on this page and we'll repost it in the comments section.

Here is a file you can use as a starting point. It just contains a list of 12 players.

Excel File Icon 2021 xlsx Equal Playing Time Challenge.xlsx

The Prize

Everyone that posts an answer will enter a drawing to win a license of our new Hero Tools Add-in ($199 value). If you already have Hero Tools you can apply the $199 towards any of our other products or programs.

Entries must be made by next Tuesday December 7th at midnight PST to qualify for the drawing.

Have fun and good luck! 馃檪

Note: It takes us time to approve the comments on the post after you submit them. So you do not need to submit it twice. Thanks for your patience. 馃檪

The Results

Wow, we are so impressed with the participation in the challenge! A big THANK YOU to everyone that participated! 🙏

We've done our best to capture all of the submissions and you can browse through them below. Each participant has a section with a screenshot of their file, their explanation, and a link to download their file.

There were over 40 files submitted/shared, and a lot of different techniques used to solve the problem. This includes formulas, pivot tables, Power Query, Power Pivot, and VBA macros.

I always say that there are a million ways to solve a problem with Excel, and this challenge was no exception. I encourage you to download a few of the files that peak your interest, and dissect them to learn how they work.

Howard White

Description:

Created drop downs for # games; periods/game; and # of players with data validation. A 54 by 5 matrix of consecutive numbers restarted at the number of players. This matrix is accessed for the index of players using the number of periods /game as offset.

The display is limited for number of periods with conditional formatting.

File:CONTEST-1.zip

Diane E Peacock

Description:

Using VBA, I used an input box to gather the information then created a worksheet for all games entered, the number of players, and the number of periods per game.

The first sheet is challenge directions, Go to a new sheet, then run Insert_Info. Enter the input questions and the worksheet will be created. I did not use the file you gave us as a starting point. Oops. I forgot about the file until now. I have learned VBA on my own with a lot of help from Webinars so my code may not be up to your standards.

It was fun & at least I tried and was successful. Thanks, Diane Peacock

File:Challenge-work.zip

David N

Description:

Spill arrays to create lists of roster/player numbers, games, and periods followed by a single spill array to take a delimited set of the roster numbers five at a time from a repeating sequence such that the roster loops back on itself. For example, the first six periods would be players 1-5, 6-10, 11-3, 4-8, 9-1, and 2-6. Unfortunately, this means some positions in the roster will never play as part of the same group, but the bottom half of the roster could be inverted mid-season to address that without too much impact to the equal playing time requirement.

File:Excel-Campus-Equal-Playing-Time.xlsx

Johan Ayala

Description:

Fun challenge!

Six worksheets, one for each game to improve readiness.

1. Each Game sheet contains the calculation of the number of spots available and one solver button to solve the equation for the coach.

2. It can be adjusted manually if is required. Indicative conditional format is in place.

3. From Game 2 each game sheet add up the previous sum game.

Thank you, Johan

File:Johan_Ayala_EqualPlayingTimeChallenge.xlsm.zip

Wayne Edmondson

Description:

Made 6 tables / games. Players in the rows; periods in the columns. Filled each period from top to bottom with 5 players in order and circulating back to the top when required. Done this way, the first 9 players in each game play 4 periods and the last 3 players 3 periods. Each subsequent game is started with the last 3 players from the prior game. The final tally after 6 games done in this way produces the result that Players 1-6 play in 23 periods and Players 7-12 play in 22 periods (see final table at bottom of worksheet).

File:Equal-Playing-Time-Challenge-Wayne-Edmondson.xlsx

Christian White

Description:

Could have done more to protect it and make it user-friendly, but my focus was on getting something that worked.

It's able to adjust for number of periods, number of players per period, and total number of players…

It finds out how many “sessions” would be needed by each player to full “staff” the entire game, and then it rounds that down to find the minimum number of periods each player should get.

To balance over any number of games, those players who did not play the maximum number of allowed games gets “equity”. The (very sloppy) VBA code I wrote up to assign to periods reorganizes the data so that players with the least amount of equity are sorted to the top of the table. Over time, this should balance out the amount of time every player plays.

File:EqualPlayingTimeChallenge-4.zip

Jorge Antonio Gomez Fabrega

Description:

I would like to explain the functionality of the spreadsheet that I have designed in detail but I am Spanish and my English level is not good enough.

However, I am going to try to mark the basis of the spreadsheet.

When you open the spreadsheet, the worksheet activated is the “Roster” sheet. You have to press the “Open Userform” button to show an userform in which you have to indicate three numbers:

– The first one is the number of players of the team (it can't be less than 5).

– The second one is the number of games of the season

– The third one is the number of periods of a game.

When you have marked those numbers, you have to press the “Generate” button.

Then, you have to press the drag-down button of the yellow combobox and mark the number of the game that you want to know the roster and finally you have to press the drag-down button of the green combobox and mark the number of the period that you want to know the roster.

When you have marked that information, you are going to be able to see that there are 5 players on the basketball court.

That is possible because of the macro I have designed, which, among other things, it calculates randomly each player of the roster so that one player can't play in the same period twice.

File:EqualPlayingTimeChallenge.zip

Eug猫nia Herrera

Description:

I have solved the challenge with a easy Excel File, without macros and enough simple to be managed by someone with limited knowledge of Excel. I didn't use formulas only available in 365 in order to keep the compatibility with previous versions. The user only have to change the data: Players on the team, Games in the season (G), Periods per game (P), Players who play in each period. And when you change these variables the format of the schedule adapts and you can see the new results. The format of the schedule also adapts to the data set. If you want you can also set the names of the players. As a bonus, I inserted a Pivot Table and a Graphic to see the results. You can filter easily with the slicers. You will see that the distribution is as equal as possible. In Schedule sheet you can see the list of the players and the games and periods played. In Schedule 2 the display is different, but you also can filter by game, period or player to view the periods played by each player.

I hope that you like it and find useful!

File:EqualPlayingTimeChallengesolvedEH.xlsx

KW YU

Description:

There are total 6*9*5=270 player attendance in which 6 players(P1) attend 22 periods and the other 6 players(P2) attend 23 periods.

An array of 270 players containing 22 cycles P1 and 23 cycles of P2 alternately is set. The array is then allocated to 6 sets with groups of 5 players for each of 9 periods and shown on ranges of cells.

File:Equal_Playing_Time_Schedule.zip

Willie Stevenson

Description:

I expanded on my last submission adding a Team listing with dropdown choices and a 6 game season. Each game roster can be unique with the ability to move players into other positions or add alternates, however the 6 games in the baseline spreadsheet equalizes the playing time as much as possible.

My solution is based on the SEQUENCE function wrapped in XLOOKUP to extract sample player names from a lookup table. Some conditional formatting (CF) is used to help the coach identify individual players per game and verifying quasi-equal playing time. CF is also used in the Roster list to identify any duplicate names.

The Summary sheet counts the number of occurrences of each player for the 6 games (from the 6 sheets) with the total displayed to verify the baseline 270 players*periods*games.

I used the SEQUENCE function to develop a dynamic range of values modified with IFS to repeat the player lineup based on the # of players, # of periods and number of players per game, all of which are user supplied variables. A dropdown list provides a visual indication of a player in each period. A second sheet counts the # periods per player for a game.

File:Player-roster-with-Names.xlsx

Hussein Satour

Description:

In this file, there are 3 parameters : number of players, number of players by period and number of periods. The solution is simple:

1- Add list of the total number of players for the game (ALL)

2- Repeat the list of players from 1 to last player until ALL

3- Add list of periods by using the function Quotient (after dividing the number of periods by ALL)

You will find more details on the file.

File:Equal-play-time-challenge-Hussein-Satour.xlsx

Shipsales

Description:

With a mix of xl formulae, and minimal VBA, the spreadsheet will display:

1. As equal playing periods for each player per game as possible

2. Offset player mix by game maximizes player participation parity over the season

3. Ensure “players on court” can't exceed team roster size

4. Ensure “Game Number” can't exceed number of games selected by coach

5. Adjustable for roster size, on-court size, nr of games/season, and nr of periods/game.

Please push the “Refresh Roster” button after any parameter changes. What a GREAT challenge, Jon! Bring on the next brain-teaser!

File:EPTC – Shipsales Input.xlsm

St茅phane

Description:

Bonjour Voici un fichier en fran莽ais, j'esp猫re qu'il sera compr茅hensible par tous.

Je propose 3 solutions : – par formules Excel365 utilisant principalement SEQUENCE, ENT, MOD, INDEX et les r茅f茅rences de plages avec # – par Power Query – par DAX

Formule Power Query

File:2021_12_02-challenge-temps-de-jeu.xlsx

Exce位ambda Cezar Ripanu

Description:

Equal playing time challenge. Study, statistics, strategies

File:Exce位ambda-GAME-function.xlsx

Peter Gulstad

Description:

I have used Dynamic Arrays to create number of players per Round and Number of Periods per Game.

A list of Players created as an official Excel Table then serves as Data Validation of name to input.

File:Equal-Playing-Time-Excel-Challenge.xlsx

RMG

Description:

File:Game.xlsx

Jayashree Patil

Description:

Used color coding to keep track of players in the groups in order to develop a pattern which was used for equal distribution.

File:Answer-to-the-Excel-challenge.xlsx

Decimo Mazzocato Jr

Description:

I used “trial and error” approach to solve the problem

In 9 PERIODS with 5 PLAYERS each, we have a total of 45 “players” needed as there are only 12 PLAYERS, each one has to play 3 or 4 times each PERIOD so, 9 PLAYERS ply 4 times each GAME, and the other three play 3 times. Total = 9*4 + 3*3 = 45 “players” each player has at least one period to rest between periods to play

The great total on column BR, shows that players will have almost the same playing periods (about 1% difference between them).

File:EqualPlayingTimeChallenge1.xlsx

Robert

Description:

The key to dividing up the players evenly is by using the INDEX-FILTER-SEQUENCE method.

This program is setup to 15 players max, and a selectable number of Innings (Periods) to 9 and selectable Number of players on the Field.

File:Baseball-Field-planner.xlsx

Ron Colling

Description:

I used a boring, non-formula approach using a pattern.

The table created can be filtered by game and period to show the players for each period.

Each of the 12 players plays 22 or 23 periods in the 6-game season.

File:EqualPlayingTimeChallenge-Ron_Colling.xlsx

Tom Juarez

Description:

Hi Jon,

I went really simple with this. No super advanced formulas, just a table, and conditional formatting.

Since it was so basic I decided to give myself an extra challenge by adding the parameter that no player could play in two periods in a row. This isn't reflected in the table – I just wanted to see if I could do it. 馃檪

There are 54 total periods and 12 players. Because there can only be 5 players per period there are 45 “slots” to be filled per game. Six games mean the total slots is 270. 270 divided by 12 is 22.5 so the closest to even playing time is either 22 or 23 total periods per player for the season.

I like visual representations, so I used conditional formatting to change the cell colors if e.g. there were too many players in a period or if a player was assigned more than 23 or less than 22 total periods in the season.

This was fun – thanks!

File:Excel-Team-challenge_tjuarez.xlsx

Fred Bevan

Description:

This is my 3rd (and I promise my last) submission.

I simplified my formula for calculating which player will be assigned to each shift by using the LET() function to break it into multiple steps.

The main complication is because I think it is important to allow the coach to mark players as absent for specific games and to have the scheduling automatically update.

I would be curious to know if anyone comes up with a simpler approach.

This is my second submission.

I updated the file so that the coach can identify players that are absent for specific games. The new version automatically excludes absent players from being assigned shifts for that game. This complicated the formulas a bit but it is an important feature.

File:Solution-Equal-Playing-Time-Challenge-V3-Fred-Bevan.xlsx

Oyekunle SOPEJU

Description:

Hi, The solution will provide players appearances for 53 periods using a central tendency of 6. [ (1+12)/2 = 6.5 ].

The coach is advised to pick the 5-man team for the 54th period from players with lowest number of appearances. i.e. players 1, 3, 6, 7 and 9.

File:EqualPlayingTimeChallenge-OS.xlsx

KENNARD HACKETT

Description:

Kept it simple with Conditional formatting to help visualize who's playing and if the correct numbers of players are in each period.

File:Ken-Hackett-Excel-Challenge-Equal-Playing-Time.xlsx

Edward McMahon

Description:

File Team Roster Problem.xlsx takes the input variables in yellow and computes the 鈥渆asy鈥 rotational player assignments that can be done evenly. The total number of periods is the number of games times the number of periods per game. If this total is evenly divisible by the number of players, then a simple rotation schedule solves the problem, as shown in R5:AC17 repeated as necessary.

If there are periods remaining after assigning the 鈥渆ven鈥 periods, then the maximum and minimum number of periods per player is calculated (and differ by 1 to solve the problem).

The schedule matrix for the remaining periods is calculated as a dynamic array (E3#) and solved with the GHG option in the Solver add-in. This solution technique will replace the dynamic array with the solution, so the associated row and column calculations no longer work. The row and column sums are added to the array.

In Solver, the total number of player-periods K18 is set equal to that required, which is shown in B16. Solver cannot accept a cell reference in the objective funct4ion when set to a value.

The constraints are that the schedule matrix (grid) must be binary. The column sums must each equal players/period (5 in cell B4), and the sum of the periods played by each player must lie between the maximum B19 and the minimum B20.

This sheet Team Roster Problem is set up to be solved for the example given. Just invoke solver. The result is given in Excel sheet Team Roster Problem Solved.xlsx

Alternatively, the schedule matrix can be simply constructed without using SEQUENCE, labeled correctly, and solved as above. This is shown in Team Roster Problem Direct.xlsx. Knowing the min and max for the schedule makes it easy to generate a solution manually by inspection.

File:team-roster-problem.xlsx

Matthias

Description:

Hi Jon, I ❤ Power Query so thanks a lot for the challenge!

I solved it in Power Query in two ways first for Player 1 to 12 and then with 12 random names. Makes it more practical and is only slightly different.

The core elements are:

-Cartesian Product to get all possible combinations of games, periods and available slots

-Column with 12 repeating numbers created with Index and Modulo

Power Query offers total flexibility for all parameters. What if there are only 10 players on a team or 4 periods in a game? Just adapt the parameter tables accordingly, so it can be helpful for those who would be using this indeed for coaching, and not just as a fun Excel challenge. 🙂 Questions or suggestions: https://www.linkedin.com/in/matthiasfriedmann

File:EqualPlayingTimeChallenge-Matthias-Friedmann.xlsx

Henry Chan

Description:

In the attached workbook, there are three worksheets:

3. Parameters & calculations

2. Roster – Table

1. Roster – Matrix

In Parameters & calculations, I did some initial calculations to work out the ideal equal playing periods per player in each game to be 3.75. Obviously, there is a fractional problem to deal with. Hence, the closest solution is to have 3 players with 3 periods and 9 players with 4 periods in each game.

There are also some balancing calculations to work out for the 6 game season as a whole. The most equal distribution for the season is to have 6 players with 22 periods and 6 players with 23 periods each.

In order to assign the actual players to each period for each game, the Roster – Table worksheet adopts the simple strategy to assign each player in turn to the next available “vacant” slot sequentially for all available game/period/player spots. There are 270 such spots. Assigning each player in turn gives them the best chance to equalize their participation rate.

The Roster – Matrix worksheet shows a visual view of the roster. The row and column totals confirm that the overall objective is achieved and the numbers align to the initial analysis in the Parameters & calculations worksheet.

This roster model is entirely parameters driven. Calculations are made using tables and formulas. The model runs in classic Excel 2013, without VBA nor “advanced” and recent Excel 365 functions.

File: EqualPlayingTimeChallenge-HC_2021-12-03.xlsx

Eddy

Description:

File: Game-Challenge.xlsx

Greg Haugeto

Description:

I have submitted a solution but did not add a description 鈥 so here it is:

My solution contains three sections:

1) Team Roster 鈥 Table containing the players sequential # and their name;

2) Player鈥檚 Individual Schedule 鈥 Select the players # and an individual schedule is displayed showing which games and periods the player will play in;

3) Master Roster containing an individual line for each game and each, period showing the players that will play that period. Hidden are dynamic columns that contain the player number that will be playing in a specific period.

Players are added sequentially to each row of the Master Roster. Once the maximum # of players (for a given period) is reached, a new period is created and players are assigned to that period.

ex) Game 1 Period 1 Players 1, 2, 3, 4, 5

Game 1 Period 2 Players 6, 7, 8, 9, 10

Game 1 Period 3 Players 11, 12, 1, 2, 3

Game 6 Period 9 Players 2, 3, 4, 5, 6

This process continues until all periods for all scheduled games are accounted for.

The Master Roster can be updated by changing 1) # of Games; 2) # of Periods; 3) # of Players per Period; and 4) # of Players on the Team.

The # of Players on the team can be adjusted simply by adding or subtracting from the TeamRoster Table.

I wish I would have had this knowledge back when I was coaching youth sports (Many Moons Ago). This is a revision of my original submission, as there was an issue with the Filter Function Trimming trailing spaces.

Thanks,

Greg

File:EqualPlayingTimeChallenge-HaugetoGreg-Revised.xlsx

Gurvinder

Description:

Dear Jon,

I have sent two files, second file is based on input in your mail. First file is also correct but I filled in different inputs. I have first taken calculation of total number of periods for all the games. (basis three variables with yellow background in the INPUT SHEET) as under:

No. of Games per period * No. of Periods * No. of Players per period.

Total periods is then divided by Fourth variable i.e. number of players out of whom selection to be made for each period to get number of periods per player. which may be fractional.

I used ROUNDUP and ROUNDDOWN functions to get two allocations so that the allocation for each player is almost equal. The difference can be 1 or nil period/allocaiton.

Then using MMULT function I calculated as to how many players will have one allocation more and how many one less.

In the Output sheet I captured the list through SEQUENCE FUNCTION. Then using IF function I captured no. of allocations per player dynamically.

I then created three column sets starting from 1 which can be up to 100 horizontally.

Basis Total Number of games to be allocated for each player, before first allocation, I ranked players 1 through the total number using RANK function (using COUNTIF function to take care of duality in ranking)

Top 5 ranked players (i.e. with maximum balance allocations) will get automatically selected through IF function (in second column in the three column set). The value in the cell will be 1 for selected Player and 0 for not included player for that period.

Last column (third) is total games for allocation MINUS value in the second column 鈥 which will be 1 for selected player and 0 for not included.

In every band this process has been copied. You may find that in last allocation all players will have 0 balance allocation. Since the number of allocations are prefilled, the ranking based on balance allocations will always make selection accurate.

Finally, In the Input worksheet I have used certain array functions to get data in horizontal form OUTPUT sheet and then I have placed the output vertically through transpose function. Vertical output shows game wise and period wise allocation. You can check the dynamic functioning of the model, by changing yellow background cells as per restrictions given in the red ink.

Thanks and Regards

File:EQUAL_PLAYING_TIME_CHALLENGE_GURVINDER_SINGH_KHARBANDA1.xlsx

Steven Makowski

Description:

I choose a roster of 10 with 8 periods, 6 games and 5 players per period. No subs. Used a pivot table to select games 1 thru 6 with set rosters. I would love to see other solutions if they are available soon.

File:Equal-Playing-Time1.xlsx

Jeph Asare

Description:

Not used excel PQ for a long time so I decided to be rigid in my analysis. It will be good to see how it goes and how others have also attempted it.

File:EqualPlayingTimeChallenge_Jeph-A.xlsx

Dennis

Description:

File:Equal-Playing-Time.xlsx

Stephen Larson

Description:

File:EqualPlayingTimeChallenge.xlsx

Marvin Parsons

Description:

The problem comes down to making the total number of periods played for all players as equal as possible for the season of 6 games. Math wise you could use a standard deviation of the total numbers of periods played for the 12 players. In this problem there are 5 players for 9 periods, so a total of 45 periods per game. Then with 6 games there are 6 times 45 or 270 total periods total. With 12 players each player needs 22 periods played and some get a 23rd period. This is a random selection. There is no exact answer but a best answer based on my workbook.

File:Baseball-Player-Schedule.xlsx

Susie Bamford

Description:

File:Book9.xlsx

Ken Hsu

Description:

File:EqualPlayingTimeChallenge-Ken-Hsu.xlsx

Enrique Garcia Calderon

Description:

File:EqualPlayingTimeChallenge-enrikx.xlsx

Greg Herrman

Description:

File:EqualPlayingTimeChallenge-GHerrman.xlsx

Eytan K

Description:

I have four pages to my spreadsheet.

Data

This page lists all of the information, the number of games, periods per game, and slots per period. As well as number of players on the team. Everything in yellow can be changed and the rest of the data updates with it. The big table shows each period in each game that each player plays in.

This will also show how many periods a player will play per game and per season, and how many players will play an additional period per game and per season. With the data that we were initially given, each player will play 22 periods this season, and six of the players will get an additional period this season.

By Game

This page shows the roster by the game marked in B1. Additionally, you can set the the period in H1 via drop down list to show the roster for that specific period.

By Player

Here you can find every period in every game the player in A1 is playing. A1 is a drop down list, that updates when the number of players is changed.

Roster for season

This shows the entire season, every game and every period, and who is playing which slot.

All of these sheets update as the data is updated, so when the number of games change, or periods, slots, or number of players, the rosters update as well.

File:Roster Problem.xlsx

Jean-S茅bastien

Description:

As mentioned by others, figuring the total number of spots is the key. For this example, it adds to 270 spots. By entering the number of games, number of periods by game, number of players by period and the number of periods, my file generates a list of all the spots. From this table, I created 2 pivot tables. One base on players and one based on games. We can then generate the individual schedule of each player and also the schedule of each game. I added a players list to add the name of the players.

File:

Jon Wittwer

Methods used:

Description:

Interesting and fun Excel challenge. 🙂

I coached soccer for a number of years (ages 6 鈥 12) and made a template for a soccer roster and lineup to help plan position rotations and playing time. Mathematical rotation schemes are a great starting point. A couple of things quickly complicate them though. The first is absences (and especially unplanned absences). Also, when you get into the older leagues where players end up preferring specific positions, it鈥檚 not just about whether they are playing, but what position they are playing 鈥 and ensuring that players get to rotate into other positions now and then (an important part of player development). And while winning isn鈥檛 everything, the coach needs to consider the abilities of each player in the lineup. So my solution was to allow flexibility in the lineup while using Excel to help identify errors and track stats (players not assigned a position or players assigned to 2 positions).

The template on the following page was adapted for use in basketball, and you inspired me to add in a playing time worksheet for tracking the overall playing time throughout the season. This is something I鈥檝e done in my own spreadsheet, but now it鈥檚 part of the template: https://www.vertex42.com/ExcelTemplates/basketball-roster.html

Based on numbers of players in attendance and unplanned absences and even injuries, ensuring equal playing time for a single game rarely happens, but tracking throughout the season allows you to plan for the next game(s) and helps to justify your decisions to parents who think their child may not be getting equal playing time.

How to consider absences vs. overall playing time throughout the season can be tricky. The thing I found that made things fair is that an absence counted as playing X quarters during that game where X was the minimum quarters played by any other player (which is usually 2). I鈥檓 sure there are other ways to do it, but that worked for my teams.

Hopefully this will be helpful for those who would be using this for coaching, and not just a fun Excel challenge. 🙂

File:basketball-roster.xlsx

Bill Hladik

Description:

If you鈥檇 like to learn more about this app see my Sway: https://sway.office.com/78P4vbWu7YMMwG7d

File:scheduling-123 – Equal Playing Time.xlsx

Mateusz

Description:

File:Lazy Solution.xlsx

  • This is a simple rotation of players throughout the periods in the game so each game only 3 players don’t get to play 4 periods. A roster rotation after each game allows for different players to play 4 periods in a game.

    The weakness is that in games 5 and 6 the roster and rotation are the same as games 1 and 2; thus a few players will not play as many periods in the season as other players.

    Simple and easy to use. Could be a starting point for a better solution.

  • The problem comes down to making the total number of periods played for all players as equal as possible for the season of 6 games. Math wise you could use a standard deviation of the total numbers of periods played for the 12 players. In this problem there are 5 players for 9 periods, so a total of 45 periods per game. Then with 6 games there are 6 times 45 or 270 total periods total. With 12 players each player needs 22 periods played and some get a 23rd period. This is a random selection. There is no exact answer but a best answer based on my workbook.

  • Just submitted my very messy attempt, thanks for the challenge! I managed to put something together that reacts very well to changes in the number of players, games, periods per game, and players per period. Gives the coach a nice highlighted roster for each period.

  • Not used excel PQ for a long time so I decided to be rigid in my analysis. It will be good to see how it goes and how others have also attempted it.

  • Interesting and fun Excel challenge. 馃檪

    I coached soccer for a number of years (ages 6 – 12) and made a template for a soccer roster and lineup to help plan position rotations and playing time. Mathematical rotation schemes are a great starting point. A couple of things quickly complicate them though. The first is absences (and especially unplanned absences). Also, when you get into the older leagues where players end up preferring specific positions, it’s not just about whether they are playing, but what position they are playing – and ensuring that players get to rotate into other positions now and then (an important part of player development). And while winning isn’t everything, the coach needs to consider the abilities of each player in the lineup. So my solution was to allow flexibility in the lineup while using Excel to help identify errors and track stats (players not assigned a position or players assigned to 2 positions).

    The template on the following page was adapted for use in basketball, and you inspired me to add in a playing time worksheet for tracking the overall playing time throughout the season. This is something I’ve done in my own spreadsheet, but now it’s part of the template: https://www.vertex42.com/ExcelTemplates/basketball-roster.html

    Based on numbers of players in attendance and unplanned absences and even injuries, ensuring equal playing time for a single game rarely happens, but tracking throughout the season allows you to plan for the next game(s) and helps to justify your decisions to parents who think their child may not be getting equal playing time.

    How to consider absences vs. overall playing time throughout the season can be tricky. The thing I found that made things fair is that an absence counted as playing X quarters during that game where X was the minimum quarters played by any other player (which is usually 2). I’m sure there are other ways to do it, but that worked for my teams.

    Hopefully this will be helpful for those who would be using this for coaching, and not just a fun Excel challenge. 馃檪

    • Good idea to use Sequence. I don’t know how to turn it into a table. Still possible to create a pivot table but numbers are not as significant. For example, period 3 of game 2 gets number 12.

  • Dear Jon, I have sent two files, second file is based on input in your mail. First file is also correct but I filled in different inputs.

    I have first taken calculation of total number of periods for all the games. (basis three variables with yellow background in the INPUT SHEET) as under:

    No. of Games per period * No. of Periods * No. of Players per period.

    Total periods is then divided by Fourth variable i.e. number of players out of whom selection to be made for each period to get number of periods per player. which may be fractional.

    I used ROUNDUP and ROUNDDOWN functions to get two allocations so that the allocation for each player is almost equal. The difference can be 1 or nil period/allocaiton.

    Then using MMULT function I calculated as to how many players will have one allocation more and how many one less.

    In the Output sheet I captured the list through SEQUENCE FUNCTION. Then using IF function I captured no. of allocations per player dynamically.

    I then created three column sets starting from 1 which can be up to 100 horizontally.

    Basis Total Number of games to be allocated for each player, before first allocation, I ranked players 1 through the total number using RANK function (using COUNTIF function to take care of duality in ranking)

    Top 5 ranked players (i.e. with maximum balance allocations) will get automatically selected through IF function (in second column in the three column set). The value in the cell will be 1 for selected Player and 0 for not included player for that period.

    Last column (third) is total games for allocation MINUS value in the second column – which will be 1 for selected player and 0 for not included.

    In every band this process has been copied. You may find that in last allocation all players will have 0 balance allocation. Since the number of allocations are prefilled, the ranking based on balance allocations will always make selection accurate.

    Finally, In the Input worksheet I have used certain array functions to get data in horizontal form OUTPUT sheet and then I have placed the output vertically through transpose function. Vertical output shows game wise and period wise allocation. You can check the dynamic functioning of the model, by changing yellow background cells as per restrictions given in the red ink.

    Thanks and Regards

  • I have submitted a solution but did not add a description – so here it is: My solution contains three sections:
    1) Team Roster – Table containing the players sequential # and their name;
    2) Player’s Individual Schedule – Select the players # and an individual schedule is displayed showing which games and periods the player will play in;
    3) Master Roster containing an individual line for each game and each, period showing the players that will play that period. Hidden are dynamic columns that contain the player number that will be playing in a specific period.

    Players are added sequentially to each row of the Master Roster. Once the maximum # of players (for a given period) is reached, a new period is created and players are assigned to that period.
    ex) Game 1 Period 1 Players 1, 2, 3, 4, 5
    Game 1 Period 2 Players 6, 7, 8, 9, 10
    Game 1 Period 3 Players 11, 12, 1, 2, 3

    Game 6 Period 9 Players 2, 3, 4, 5, 6

    This process continues until all periods for all scheduled games are accounted for.

    The Master Roster can be updated by changing 1) # of Games; 2) # of Periods; 3) # of Players per Period; and 4) # of Players on the Team.

    The # of Players on the team can be adjusted simply by adding or subtracting from the TeamRoster Table.

    I wish I would have had this knowledge back when I was coaching youth sports (Many Moons Ago).

  • There are 54 periods to be played by 12 players. During one game we could have a split of 9 players playing 4 times and 3 players playing 3 times. During all games, we could have a split of 6 players playing 22 times and 6 players playing 23 times. This is closest I could get :). So we cannot split 100% equal, but as close as possible, hoping that at the end of the games noone counted and they get the feeling they played equally 馃檪

    • Alternative, they can play between them, all 6 games and all 9 periods, 5 players/ period for one team, 5 players/period for second team, this would mean 45 games each, perfectly equal.

  • As mentioned by others, figuring the total number of spots is the key. For this example, it adds to 270 spots. By entering the number of games, number of periods by game, number of players by period and the number of periods, my file generates a list of all the spots. From this table, I created 2 pivot tables. One base on players and one based on games. We can then generate the individual schedule of each player and also the schedule of each game. I added a players list to add the name of the players.

    https://planoramaca.sharepoint.com/:x:/s/PLANORAMA/EYwYx9LYXZpAlgnDxq9sbQ0Bw4FesfHoxM1D2_MHmtJm3g?e=e2iash

  • https://onedrive.live.com/view.aspx?resid=32AADCC0B968432A!3566&ithint=file%2cxlsx&authkey=!AOY03ZjKlaEs6Qs

    I have four pages to my spreadsheet.

    Data
    This page lists all of the information, the number of games, periods per game, and slots per period. As well as number of players on the team. Everything in yellow can be changed and the rest of the data updates with it. The big table shows each period in each game that each player plays in.

    This will also show how many periods a player will play per game and per season, and how many players will play an additional period per game and per season. With the data that we were initially given, each player will play 22 periods this season, and six of the players will get an additional period this season.

    By Game
    This page shows the roster by the game marked in B1. Additionally, you can set the the period in H1 via drop down list to show the roster for that specific period.

    By Player
    Here you can find every period in every game the player in A1 is playing. A1 is a drop down list, that updates when the number of players is changed.

    Roster for season
    This shows the entire season, every game and every period, and who is playing which slot.

    All of these sheets update as the data is updated, so when the number of games change, or periods, slots, or number of players, the rosters update as well.

  • Fun challenge!

    I think the key to making the spreadsheet useful in a real setting is to make it flexible. However, with flexibility comes complexity so I focused on the parameters that I thought are most likely to change.

    Can remain static (these are probably set by the league):
    – Number of periods per game
    – Number of players per shift

    Must be configurable and the schedule should update automatically:
    – Number of players on the team
    – Attendance of specific players at each game

    I have already submitted my file based on these criteria. Here is a link:

    https://1drv.ms/x/s!AiTHsE7PlVVN-SyFeISwJwAeg6ef?e=9f46nx

  • – My solution is straightforward as these are 5-year-olds.
    – I split the periods into 1/2’s, or segments.
    – Everyone gets 7 – 8 segments per game.
    – My segments are mainly contiguous in order to mitigate the number of lineup changes, for the coaches’ sake.
    – No time per quarter was given, however, since they are 5-year-olds, I will assume they are minimal in lengthen.
    – I could split these up into smaller chunks if players aren’t physically able to play in contiguous segments.
    – Players can draw numbers for segment positions.

    Would love to win and apply the amount to an annual training subscription.

    Happy Holidays,

    Phil

  • Your solution creates an issue with regards to the number of shifts to be played over the course of the six games. Given that players who miss a game should not expect an equal number of shifts, it should also be a given that those who do attend, should expect to play a close number of shifts. In one example, if four players (1; 2; 11; 12) miss one game each, the remaining players receive a skewed number of shifts. In one case, the variation is between 20 (Player 3) and 27 (Player 6) shifts for full attendees.
    Found a problem, don’t have the solution (LOL).

  • Hi Jon. Thank you for all of your great work (and those who submitted solutions!). I have a question that is probably less-related to the concept of this article, but I’m not sure that any topics directly relate to what I am going to ask about so here it goes.

    We are trying to plan for a multi-day training series for next year and we want to identify a few different things related to this. 1) Which day(s) of the week are preferred; 2) How many people we can expect to have at the training(s). NOTE that there is a cap to this event so if one fills up we would offer another cohort of the series on a different day (i.e. if Mondays filled up then we would open another on Tuesdays, etc – although it doesn’t necessarily need to go in chrono order). Let’s just say we have a cap of 40 for each possible cohort, and need a minimum of 30 for each to run.

    We plan on sending out a survey to all of the potential organizations that we want to attend and are going to instruct ONE person to respond on behalf of that organization identifying all of the days of the week in which they could send participants (ranked by preference), as well as the total number of possible participants if it is offered as only one cohort AND if multiple cohorts are offered (some may be able to send additional people if multiple cohorts are offered, as opposed to sending all on the same day). We also are collecting their preference on if they want all of their people to be on the same day or split across days (or no preference). Ultimately, we want a way to identify how many cohorts should be offered and on which days, to accommodate the maximum number of people interested. Does something like this exist in the field already? Otherwise, I’m sure something like this could be created in Excel. I appreciate any and all help with this!

  • I know I am so late to the party that everyone had already packed up and gone home, but I saw this challenge after the close and thought it would be fun to do. Here’s my attempt for what it’s worth: https://1drv.ms/x/s!Au2-5eJ-YBrHkcU5AdnlxUCXTUPVhA?e=OpoBaK

    I used VBA to create the game boards and you can choose the number of players, games and periods, but there can only be up to 5 players in a period. It adds basic formatting to make the boards stand out a little.

  • Hello Jon,
    I’m curious how YOU would have completed this challenge. What came to your mind first?
    Thanks,
    Diane

  • Great challenge 馃檪
    This is a VERY good idea, to enable us to learn from others’ solutions. Thanks Jon

    Unfortunately, there is no link to Jean-S茅bastien’s file and I cannot acces the location the link in is comment points at.
    Could you please add a link to his solutions.
    Thanks in advance
    BR
    Dany

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >