Excel Challenge: Equal Playing Time

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 “easy” 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 “even” 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’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). 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’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. 🙂

File:basketball-roster.xlsx

Bill Hladik

Description:

If you’d 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