Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 24th 09, 04:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 1
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old September 24th 09, 09:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 2
Default 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   Report Post  
Old September 24th 09, 09:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2009
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Schedule Help GoBucks Excel Discussion (Misc queries) 0 June 27th 06 03:28 PM
How do I set up a tennis schedule.same time, same day diff week? sgiovinc Charts and Charting in Excel 0 April 3rd 06 07:10 PM
Create patient schedule based on master therapist schedule PapaBear Excel Discussion (Misc queries) 8 October 12th 05 04:56 AM
How do I set up a Doubles Tennis roster for 12 players? AliJax New Users to Excel 3 September 17th 05 12:31 AM
Schedule? Cognition Excel Discussion (Misc queries) 1 September 14th 05 08:31 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017