Remember Me?

#1
September 24th 09, 04:58 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2009 Posts: 1
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?

#2
September 24th 09, 06:37 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,101
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?

#3
September 24th 09, 06:54 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 9,101
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?

#4
September 24th 09, 07:11 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 1,696
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

"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?

#5
September 24th 09, 09:47 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2009 Posts: 2
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?

#6
September 24th 09, 09:48 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Sep 2009 Posts: 2
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

"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?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post GoBucks Excel Discussion (Misc queries) 0 June 27th 06 03:28 PM sgiovinc Charts and Charting in Excel 0 April 3rd 06 07:10 PM PapaBear Excel Discussion (Misc queries) 8 October 12th 05 04:56 AM AliJax New Users to Excel 3 September 17th 05 12:31 AM Cognition Excel Discussion (Misc queries) 1 September 14th 05 08:31 AM

All times are GMT +1. The time now is 10:59 PM.