Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tennis Schedule - Schedule players randomly
I would like to create tennis schedule that schedules the players randomly.
There are a total of 6 players. Only 4 can play on any given day. We have 41 court dates throughout the year. What kind of formula can I use to make sure the players alternate as evenly as possible? Thanks for your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tennis Schedule - Schedule players randomly
There isn't a formula. These type problems are complicated to program.
Usually trying every combination and determing a score for each combination, then picking the combination with the best score. You have 6!/(4!2!) which is 15 different ways of spliting 6 people into groups of 4. And then 3 different ways of putting 4 people into two teams of two. Which is 45 (15 x 3) different combinations. Why don't you get 4 more court dates and put the 45 combinations into a hat a just pick or just pick 41 times from the hat and 4 combinations won't get used. You wouldn't get a better random selection from picking from a hat then you would by having a worksheet do it for you. what are you calling "Evenly As Possible". "Chris M." wrote: I would like to create tennis schedule that schedules the players randomly. There are a total of 6 players. Only 4 can play on any given day. We have 41 court dates throughout the year. What kind of formula can I use to make sure the players alternate as evenly as possible? Thanks for your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tennis Schedule - Schedule players randomly
Here are the 45 combinations
Team 1 Team 2 1 2 3 4 1 2 3 5 1 2 3 6 1 2 4 5 1 2 4 6 1 2 5 6 1 3 4 5 1 3 4 6 1 3 5 6 1 4 5 6 2 3 4 5 2 3 4 6 2 3 5 6 2 4 5 6 3 4 5 6 1 3 2 4 1 3 2 5 1 3 2 6 1 4 2 5 1 4 2 6 1 5 2 6 1 4 3 5 1 4 3 6 1 5 3 6 1 5 4 6 2 4 3 5 2 4 3 6 2 5 3 6 2 5 4 6 3 5 4 6 1 4 3 2 1 5 3 2 1 6 3 2 1 5 4 2 1 6 4 2 1 6 5 2 1 5 4 3 1 6 4 3 1 6 5 3 1 6 5 4 2 5 4 3 2 6 4 3 2 6 5 3 2 6 5 4 3 6 5 4 "Chris M." wrote: I would like to create tennis schedule that schedules the players randomly. There are a total of 6 players. Only 4 can play on any given day. We have 41 court dates throughout the year. What kind of formula can I use to make sure the players alternate as evenly as possible? Thanks for your help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tennis Schedule - Schedule players randomly
I could imagine listing these 45 combinations, then using RAND() next to
them, then RANK() to get a 1 - 45 to start with anyways. May want to enter the RAND() in once, then copy/paste special values, lest they should continue to change every time you make any changes to the spreadsheet. otherwise, you can go with the standard 1 plays 2, 3 plays 4, then 5 plays 6, 1 plays 4, 2 plays 5, 3 plays 6 and so on. the assignments of teams to numbers is really your random numberat that point. "Joel" wrote: Here are the 45 combinations Team 1 Team 2 1 2 3 4 1 2 3 5 1 2 3 6 1 2 4 5 1 2 4 6 1 2 5 6 1 3 4 5 1 3 4 6 1 3 5 6 1 4 5 6 2 3 4 5 2 3 4 6 2 3 5 6 2 4 5 6 3 4 5 6 1 3 2 4 1 3 2 5 1 3 2 6 1 4 2 5 1 4 2 6 1 5 2 6 1 4 3 5 1 4 3 6 1 5 3 6 1 5 4 6 2 4 3 5 2 4 3 6 2 5 3 6 2 5 4 6 3 5 4 6 1 4 3 2 1 5 3 2 1 6 3 2 1 5 4 2 1 6 4 2 1 6 5 2 1 5 4 3 1 6 4 3 1 6 5 3 1 6 5 4 2 5 4 3 2 6 4 3 2 6 5 3 2 6 5 4 3 6 5 4 "Chris M." wrote: I would like to create tennis schedule that schedules the players randomly. There are a total of 6 players. Only 4 can play on any given day. We have 41 court dates throughout the year. What kind of formula can I use to make sure the players alternate as evenly as possible? Thanks for your help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tennis Schedule - Schedule players randomly
Joel,
Thanks for your help. I really appreciate it. I Solved it using a slightly different method but seeing the way you laid things out made it click for me. Thanks again. Chris "Joel" wrote: Here are the 45 combinations Team 1 Team 2 1 2 3 4 1 2 3 5 1 2 3 6 1 2 4 5 1 2 4 6 1 2 5 6 1 3 4 5 1 3 4 6 1 3 5 6 1 4 5 6 2 3 4 5 2 3 4 6 2 3 5 6 2 4 5 6 3 4 5 6 1 3 2 4 1 3 2 5 1 3 2 6 1 4 2 5 1 4 2 6 1 5 2 6 1 4 3 5 1 4 3 6 1 5 3 6 1 5 4 6 2 4 3 5 2 4 3 6 2 5 3 6 2 5 4 6 3 5 4 6 1 4 3 2 1 5 3 2 1 6 3 2 1 5 4 2 1 6 4 2 1 6 5 2 1 5 4 3 1 6 4 3 1 6 5 3 1 6 5 4 2 5 4 3 2 6 4 3 2 6 5 3 2 6 5 4 3 6 5 4 "Chris M." wrote: I would like to create tennis schedule that schedules the players randomly. There are a total of 6 players. Only 4 can play on any given day. We have 41 court dates throughout the year. What kind of formula can I use to make sure the players alternate as evenly as possible? Thanks for your help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tennis Schedule - Schedule players randomly
Sean,
Thanks for your help. I solved it using a slightly different approach but seeing how you laid things out made the answer click for me. Thanks again. Chris "Sean Timmons" wrote: I could imagine listing these 45 combinations, then using RAND() next to them, then RANK() to get a 1 - 45 to start with anyways. May want to enter the RAND() in once, then copy/paste special values, lest they should continue to change every time you make any changes to the spreadsheet. otherwise, you can go with the standard 1 plays 2, 3 plays 4, then 5 plays 6, 1 plays 4, 2 plays 5, 3 plays 6 and so on. the assignments of teams to numbers is really your random numberat that point. "Joel" wrote: Here are the 45 combinations Team 1 Team 2 1 2 3 4 1 2 3 5 1 2 3 6 1 2 4 5 1 2 4 6 1 2 5 6 1 3 4 5 1 3 4 6 1 3 5 6 1 4 5 6 2 3 4 5 2 3 4 6 2 3 5 6 2 4 5 6 3 4 5 6 1 3 2 4 1 3 2 5 1 3 2 6 1 4 2 5 1 4 2 6 1 5 2 6 1 4 3 5 1 4 3 6 1 5 3 6 1 5 4 6 2 4 3 5 2 4 3 6 2 5 3 6 2 5 4 6 3 5 4 6 1 4 3 2 1 5 3 2 1 6 3 2 1 5 4 2 1 6 4 2 1 6 5 2 1 5 4 3 1 6 4 3 1 6 5 3 1 6 5 4 2 5 4 3 2 6 4 3 2 6 5 3 2 6 5 4 3 6 5 4 "Chris M." wrote: I would like to create tennis schedule that schedules the players randomly. There are a total of 6 players. Only 4 can play on any given day. We have 41 court dates throughout the year. What kind of formula can I use to make sure the players alternate as evenly as possible? Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Schedule Help | Excel Discussion (Misc queries) | |||
How do I set up a tennis schedule.same time, same day diff week? | Charts and Charting in Excel | |||
Create patient schedule based on master therapist schedule | Excel Discussion (Misc queries) | |||
How do I set up a Doubles Tennis roster for 12 players? | New Users to Excel | |||
Schedule? | Excel Discussion (Misc queries) |