|
League Fixtures
Hi all,
I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. Each player only plays an opponent once every 19 weeks. Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. Can anyone help or point me in the right direction? cheers in anticipation DubboPete |
League Fixtures
Earlier there was a similar thread, maybe you can make use of my answer given
to that post. I copy here the thread: Do you mean something like this: A B C D E F week pair1 pair2 pair3 pair4 pair5 1 t1-t2 t3-t4 t5-t6 t7-t8 t9-t10 2 t1-t3 t3-t5 t5-t7 t7-t9 t9-t1 3 t1-t4 t3-t6 t5-t8 t7-t10 t9-t2 4 t1-t5 t3-t7 t5-t9 t7-t1 t9-t3 5 t1-t6 t3-t8 t5-t10 t7-t2 t9-t4 6 t1-t7 t3-t9 t5-t1 t7-t3 t9-t5 7 t1-t8 t3-t10 t5-t2 t7-t4 t9-t6 8 t1-t9 t3-t1 t5-t3 t7-t5 t9-t7 9 t1-t10 t3-t2 t5-t4 t7-t6 t9-t8 The formula in B2: ="t"&INT(2*COLUMN()-1/2)-2&"-t"&IF(MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10)0,MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10),10) Fill it to the right and down! t1, t2, etc. stands for team1, team2, etc. Regards, Stefi manchester united ezt Ã*rta: I need help trying to set a format for 10 teams playing each other in 10 weeks. eg...team1 vs team 2, team 3 vs team 4, team 5 vs team 6...etc... Also for 12 teams playing against each other for 10 weeks. Thanks for your help.... DubboPete ezt Ã*rta: Hi all, I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. Each player only plays an opponent once every 19 weeks. Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. Can anyone help or point me in the right direction? cheers in anticipation DubboPete |
League Fixtures
On odd weeks all teams are paired, buy on even weeks only teams with
odd numbers are paired, and they are paired twice. This can not be the expected result from the pairing. Lars-Åke On Mon, 27 Jul 2009 04:27:01 -0700, Stefi wrote: Earlier there was a similar thread, maybe you can make use of my answer given to that post. I copy here the thread: Do you mean something like this: A B C D E F week pair1 pair2 pair3 pair4 pair5 1 t1-t2 t3-t4 t5-t6 t7-t8 t9-t10 2 t1-t3 t3-t5 t5-t7 t7-t9 t9-t1 3 t1-t4 t3-t6 t5-t8 t7-t10 t9-t2 4 t1-t5 t3-t7 t5-t9 t7-t1 t9-t3 5 t1-t6 t3-t8 t5-t10 t7-t2 t9-t4 6 t1-t7 t3-t9 t5-t1 t7-t3 t9-t5 7 t1-t8 t3-t10 t5-t2 t7-t4 t9-t6 8 t1-t9 t3-t1 t5-t3 t7-t5 t9-t7 9 t1-t10 t3-t2 t5-t4 t7-t6 t9-t8 The formula in B2: ="t"&INT(2*COLUMN()-1/2)-2&"-t"&IF(MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10)0,MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10),10) Fill it to the right and down! t1, t2, etc. stands for team1, team2, etc. Regards, Stefi manchester united ezt írta: I need help trying to set a format for 10 teams playing each other in 10 weeks. eg...team1 vs team 2, team 3 vs team 4, team 5 vs team 6...etc... Also for 12 teams playing against each other for 10 weeks. Thanks for your help.... DubboPete ezt írta: Hi all, I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. Each player only plays an opponent once every 19 weeks. Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. Can anyone help or point me in the right direction? cheers in anticipation DubboPete |
League Fixtures
On Jul 27, 9:37*pm, Lars-Åke Aspelin
wrote: On odd weeks all teams are paired, buy on even weeks only teams with odd numbers are paired, and they are paired twice. This can not be the expected result from the pairing. Lars-Åke On Mon, 27 Jul 2009 04:27:01 -0700, Stefi wrote: Earlier there was a similar thread, maybe you can make use of my answer given to that post. I copy here the thread: Do you mean something like this: A * * * * * * *B * * * * * * *C * * * * * * *D * * * * * * *E * * * * * * *F week * * * *pair1 * pair2 * pair3 * pair4 * pair5 1 * t1-t2 * t3-t4 * t5-t6 * t7-t8 * t9-t10 2 * t1-t3 * t3-t5 * t5-t7 * t7-t9 * t9-t1 3 * t1-t4 * t3-t6 * t5-t8 * t7-t10 *t9-t2 4 * t1-t5 * t3-t7 * t5-t9 * t7-t1 * t9-t3 5 * t1-t6 * t3-t8 * t5-t10 *t7-t2 * t9-t4 6 * t1-t7 * t3-t9 * t5-t1 * t7-t3 * t9-t5 7 * t1-t8 * t3-t10 *t5-t2 * t7-t4 * t9-t6 8 * t1-t9 * t3-t1 * t5-t3 * t7-t5 * t9-t7 9 * t1-t10 *t3-t2 * t5-t4 * t7-t6 * t9-t8 The formula in B2: ="t"&INT(2*COLUMN()-1/2)-2&"-t"&IF(MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10)*0,MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10),10) Fill it to the right and down! t1, t2, etc. stands for team1, team2, etc. Regards, Stefi manchester united ezt írta: I need help trying to set a format for 10 teams playing each other in 10 weeks. eg...team1 vs team 2, team 3 vs team 4, team 5 vs team 6...etc.... Also for 12 teams playing against each other for 10 weeks. Thanks for your help.... DubboPete ezt írta: Hi all, I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. * Each player only plays an opponent once every 19 weeks. * Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. * Can anyone help or point me in the right direction? cheers in anticipation DubboPete- Hide quoted text - - Show quoted text - thanks Lars, Stefi I could not get your fromula to work at all... |
League Fixtures
You are right, I didn't notice this error, trying to fix it.
Stefi Lars-Ã ke Aspelin ezt Ã*rta: On odd weeks all teams are paired, buy on even weeks only teams with odd numbers are paired, and they are paired twice. This can not be the expected result from the pairing. Lars-Ã ke On Mon, 27 Jul 2009 04:27:01 -0700, Stefi wrote: Earlier there was a similar thread, maybe you can make use of my answer given to that post. I copy here the thread: Do you mean something like this: A B C D E F week pair1 pair2 pair3 pair4 pair5 1 t1-t2 t3-t4 t5-t6 t7-t8 t9-t10 2 t1-t3 t3-t5 t5-t7 t7-t9 t9-t1 3 t1-t4 t3-t6 t5-t8 t7-t10 t9-t2 4 t1-t5 t3-t7 t5-t9 t7-t1 t9-t3 5 t1-t6 t3-t8 t5-t10 t7-t2 t9-t4 6 t1-t7 t3-t9 t5-t1 t7-t3 t9-t5 7 t1-t8 t3-t10 t5-t2 t7-t4 t9-t6 8 t1-t9 t3-t1 t5-t3 t7-t5 t9-t7 9 t1-t10 t3-t2 t5-t4 t7-t6 t9-t8 The formula in B2: ="t"&INT(2*COLUMN()-1/2)-2&"-t"&IF(MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10)0,MOD((INT(2*COLUMN()-1/2)-1)+ROW()-2,10),10) Fill it to the right and down! t1, t2, etc. stands for team1, team2, etc. Regards, Stefi manchester united ezt Ã*rta: I need help trying to set a format for 10 teams playing each other in 10 weeks. eg...team1 vs team 2, team 3 vs team 4, team 5 vs team 6...etc... Also for 12 teams playing against each other for 10 weeks. Thanks for your help.... DubboPete ezt Ã*rta: Hi all, I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. Each player only plays an opponent once every 19 weeks. Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. Can anyone help or point me in the right direction? cheers in anticipation DubboPete |
League Fixtures
On Mon, 27 Jul 2009 03:34:59 -0700 (PDT), DubboPete
wrote: Hi all, I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. Each player only plays an opponent once every 19 weeks. Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. Can anyone help or point me in the right direction? cheers in anticipation DubboPete Here is one example of pairing of players for the 19 weeks. week no pair 1 pair 2 pair 3 pair 4 pair 5 pair 6 pair 7 pair 8 pair 9 pair 10 week 1: 1-20 2-19 3-18 4-17 5-16 6-15 7-14 8-13 9-12 10-11 week 2: 2-20 3-1 4-19 5-18 6-17 7-16 8-15 9-14 10-13 11-12 week 3: 3-20 4-2 5-1 6-19 7-18 8-17 9-16 10-15 11-14 12-13 week 4: 4-20 5-3 6-2 7-1 8-19 9-18 10-17 11-16 12-15 13-14 week 5: 5-20 6-4 7-3 8-2 9-1 10-19 11-18 12-17 13-16 14-15 week 6: 6-20 7-5 8-4 9-3 10-2 11-1 12-19 13-18 14-17 15-16 week 7: 7-20 8-6 9-5 10-4 11-3 12-2 13-1 14-19 15-18 16-17 week 8: 8-20 9-7 10-6 11-5 12-4 13-3 14-2 15-1 16-19 17-18 week 9: 9-20 10-8 11-7 12-6 13-5 14-4 15-3 16-2 17-1 18-19 week 10: 10-20 11-9 12-8 13-7 14-6 15-5 16-4 17-3 18-2 19-1 week 11: 11-20 12-10 13-9 14-8 15-7 16-6 17-5 18-4 19-3 1-2 week 12: 12-20 13-11 14-10 15-9 16-8 17-7 18-6 19-5 1-4 2-3 week 13: 13-20 14-12 15-11 16-10 17-9 18-8 19-7 1-6 2-5 3-4 week 14: 14-20 15-13 16-12 17-11 18-10 19-9 1-8 2-7 3-6 4-5 week 15: 15-20 16-14 17-13 18-12 19-11 1-10 2-9 3-8 4-7 5-6 week 16: 16-20 17-15 18-14 19-13 1-12 2-11 3-10 4-9 5-8 6-7 week 17: 17-20 18-16 19-15 1-14 2-13 3-12 4-11 5-10 6-9 7-8 week 18: 18-20 19-17 1-16 2-15 3-14 4-13 5-12 6-11 7-10 8-9 week 19: 19-20 1-18 2-17 3-16 4-15 5-14 6-13 7-12 8-11 9-10 Same thing in a more condensed format to (hopefully) avoid line length problems week 1: 1-20, 2-19, 3-18, 4-17, 5-16, 6-15, 7-14, 8-13, 9-12, 10-11 week 2: 2-20, 3-1, 4-19, 5-18, 6-17, 7-16, 8-15, 9-14, 10-13, 11-12 week 3: 3-20, 4-2, 5-1, 6-19, 7-18, 8-17, 9-16, 10-15, 11-14, 12-13 week 4: 4-20, 5-3, 6-2, 7-1, 8-19, 9-18, 10-17, 11-16, 12-15, 13-14 week 5: 5-20, 6-4, 7-3, 8-2, 9-1, 10-19, 11-18, 12-17, 13-16, 14-15 week 6: 6-20, 7-5, 8-4, 9-3, 10-2, 11-1, 12-19, 13-18, 14-17, 15-16 week 7: 7-20, 8-6, 9-5, 10-4, 11-3, 12-2, 13-1, 14-19, 15-18, 16-17 week 8: 8-20, 9-7, 10-6, 11-5, 12-4, 13-3, 14-2, 15-1, 16-19, 17-18 week 9: 9-20, 10-8, 11-7, 12-6, 13-5, 14-4, 15-3, 16-2, 17-1, 18-19 week 10: 10-20, 11-9, 12-8, 13-7, 14-6, 15-5, 16-4, 17-3, 18-2, 19-1 week 11: 11-20, 12-10, 13-9, 14-8, 15-7, 16-6, 17-5, 18-4, 19-3, 1-2 week 12: 12-20, 13-11, 14-10, 15-9, 16-8, 17-7, 18-6, 19-5, 1-4, 2-3 week 13: 13-20, 14-12, 15-11, 16-10, 17-9, 18-8, 19-7, 1-6, 2-5, 3-4 week 14: 14-20, 15-13, 16-12, 17-11, 18-10, 19-9, 1-8, 2-7, 3-6, 4-5 week 15: 15-20, 16-14, 17-13, 18-12, 19-11, 1-10, 2-9, 3-8, 4-7, 5-6 week 16: 16-20, 17-15, 18-14, 19-13, 1-12, 2-11, 3-10, 4-9, 5-8, 6-7 week 17: 17-20, 18-16, 19-15, 1-14, 2-13, 3-12, 4-11, 5-10, 6-9, 7-8 week 18: 18-20, 19-17, 1-16, 2-15, 3-14, 4-13, 5-12, 6-11, 7-10, 8-9 week 19: 19-20, 1-18, 2-17, 3-16, 4-15, 5-14, 6-13, 7-12, 8-11, 9-10 This is an example of a Round robin algorithm, see http://www.devenezia.com/downloads/r...bin/index.html The formula in cell B2 is this: =1+MOD(ROW()+COLUMN()-4,19)&"-"&IF(COLUMN()=2,20,1+MOD(ROW()-COLUMN(),19)) Copy to the right until cell K2 and then down to row 20 Hope this helps / Lars-Åke |
League Fixtures
On Mon, 27 Jul 2009 14:29:58 GMT, Lars-Åke Aspelin
wrote: On Mon, 27 Jul 2009 03:34:59 -0700 (PDT), DubboPete wrote: Hi all, I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. Each player only plays an opponent once every 19 weeks. Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. Can anyone help or point me in the right direction? cheers in anticipation DubboPete Here is one example of pairing of players for the 19 weeks. week no pair 1 pair 2 pair 3 pair 4 pair 5 pair 6 pair 7 pair 8 pair 9 pair 10 week 1: 1-20 2-19 3-18 4-17 5-16 6-15 7-14 8-13 9-12 10-11 week 2: 2-20 3-1 4-19 5-18 6-17 7-16 8-15 9-14 10-13 11-12 week 3: 3-20 4-2 5-1 6-19 7-18 8-17 9-16 10-15 11-14 12-13 week 4: 4-20 5-3 6-2 7-1 8-19 9-18 10-17 11-16 12-15 13-14 week 5: 5-20 6-4 7-3 8-2 9-1 10-19 11-18 12-17 13-16 14-15 week 6: 6-20 7-5 8-4 9-3 10-2 11-1 12-19 13-18 14-17 15-16 week 7: 7-20 8-6 9-5 10-4 11-3 12-2 13-1 14-19 15-18 16-17 week 8: 8-20 9-7 10-6 11-5 12-4 13-3 14-2 15-1 16-19 17-18 week 9: 9-20 10-8 11-7 12-6 13-5 14-4 15-3 16-2 17-1 18-19 week 10: 10-20 11-9 12-8 13-7 14-6 15-5 16-4 17-3 18-2 19-1 week 11: 11-20 12-10 13-9 14-8 15-7 16-6 17-5 18-4 19-3 1-2 week 12: 12-20 13-11 14-10 15-9 16-8 17-7 18-6 19-5 1-4 2-3 week 13: 13-20 14-12 15-11 16-10 17-9 18-8 19-7 1-6 2-5 3-4 week 14: 14-20 15-13 16-12 17-11 18-10 19-9 1-8 2-7 3-6 4-5 week 15: 15-20 16-14 17-13 18-12 19-11 1-10 2-9 3-8 4-7 5-6 week 16: 16-20 17-15 18-14 19-13 1-12 2-11 3-10 4-9 5-8 6-7 week 17: 17-20 18-16 19-15 1-14 2-13 3-12 4-11 5-10 6-9 7-8 week 18: 18-20 19-17 1-16 2-15 3-14 4-13 5-12 6-11 7-10 8-9 week 19: 19-20 1-18 2-17 3-16 4-15 5-14 6-13 7-12 8-11 9-10 Same thing in a more condensed format to (hopefully) avoid line length problems week 1: 1-20, 2-19, 3-18, 4-17, 5-16, 6-15, 7-14, 8-13, 9-12, 10-11 week 2: 2-20, 3-1, 4-19, 5-18, 6-17, 7-16, 8-15, 9-14, 10-13, 11-12 week 3: 3-20, 4-2, 5-1, 6-19, 7-18, 8-17, 9-16, 10-15, 11-14, 12-13 week 4: 4-20, 5-3, 6-2, 7-1, 8-19, 9-18, 10-17, 11-16, 12-15, 13-14 week 5: 5-20, 6-4, 7-3, 8-2, 9-1, 10-19, 11-18, 12-17, 13-16, 14-15 week 6: 6-20, 7-5, 8-4, 9-3, 10-2, 11-1, 12-19, 13-18, 14-17, 15-16 week 7: 7-20, 8-6, 9-5, 10-4, 11-3, 12-2, 13-1, 14-19, 15-18, 16-17 week 8: 8-20, 9-7, 10-6, 11-5, 12-4, 13-3, 14-2, 15-1, 16-19, 17-18 week 9: 9-20, 10-8, 11-7, 12-6, 13-5, 14-4, 15-3, 16-2, 17-1, 18-19 week 10: 10-20, 11-9, 12-8, 13-7, 14-6, 15-5, 16-4, 17-3, 18-2, 19-1 week 11: 11-20, 12-10, 13-9, 14-8, 15-7, 16-6, 17-5, 18-4, 19-3, 1-2 week 12: 12-20, 13-11, 14-10, 15-9, 16-8, 17-7, 18-6, 19-5, 1-4, 2-3 week 13: 13-20, 14-12, 15-11, 16-10, 17-9, 18-8, 19-7, 1-6, 2-5, 3-4 week 14: 14-20, 15-13, 16-12, 17-11, 18-10, 19-9, 1-8, 2-7, 3-6, 4-5 week 15: 15-20, 16-14, 17-13, 18-12, 19-11, 1-10, 2-9, 3-8, 4-7, 5-6 week 16: 16-20, 17-15, 18-14, 19-13, 1-12, 2-11, 3-10, 4-9, 5-8, 6-7 week 17: 17-20, 18-16, 19-15, 1-14, 2-13, 3-12, 4-11, 5-10, 6-9, 7-8 week 18: 18-20, 19-17, 1-16, 2-15, 3-14, 4-13, 5-12, 6-11, 7-10, 8-9 week 19: 19-20, 1-18, 2-17, 3-16, 4-15, 5-14, 6-13, 7-12, 8-11, 9-10 This is an example of a Round robin algorithm, see http://www.devenezia.com/downloads/r...bin/index.html The formula in cell B2 is this: =1+MOD(ROW()+COLUMN()-4,19)&"-"&IF(COLUMN()=2,20,1+MOD(ROW()-COLUMN(),19)) Copy to the right until cell K2 and then down to row 20 Hope this helps / Lars-Åke To make this a bit more general. Put the following formula in cell B2 and copy it right and down as far as needed. (The number of columns should be half of the maximum number of players, and the number of rows should be equal to the maximum number of players minus one). =IF(OR(ROW()CEILING($A$1,2),COLUMN()*2CEILING($A $1,2)+2),"", 1+MOD(ROW()+COLUMN()-4,CEILING($A$1,2)-1)&"-"&IF(COLUMN()=2, CEILING($A$1,2),1+MOD(ROW()-COLUMN(),CEILING($A$1,2)-1))) Then put the number of players in cell A1 and watch the pairings for different number of players. If the number of players are odd, the highest number should be considered as a "ghost player" and matches with the "ghost" player should be ignored. If you want to replace the numbers with player names, try this formula: =IF(OR(ROW()CEILING($A$1,2),COLUMN()*2CEILING($A $1,2)+2),"", INDEX(Players,1+MOD(ROW()+COLUMN()-4,CEILING($A$1,2)-1))&"-"& INDEX(Players,IF(COLUMN()=2,CEILING($A$1,2),1+MOD( ROW()-COLUMN(),CEILING($A$1,2)-1)))) where "Players" is a named range with the names of all players. Hope this helps / Lars-Åke |
League Fixtures
On Jul 28, 1:25*am, Lars-Åke Aspelin
wrote: On Mon, 27 Jul 2009 14:29:58 GMT, Lars-Åke Aspelin wrote: On Mon, 27 Jul 2009 03:34:59 -0700 (PDT),DubboPete wrote: Hi all, I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. * Each player only plays an opponent once every 19 weeks. * Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. * Can anyone help or point me in the right direction? cheers in anticipation DubboPete Here is one example of pairing of players for the 19 weeks. week no * * pair 1 *pair 2 *pair 3 *pair 4 *pair 5 *pair 6 *pair 7 *pair 8 pair 9 * * *pair 10 week 1: * * 1-20 * *2-19 * *3-18 * *4-17 * *5-16 * *6-15 * *7-14 * *8-13 9-12 * * * *10-11 week 2: * * 2-20 * *3-1 * * 4-19 * *5-18 * *6-17 * *7-16 * *8-15 * *9-14 10-13 * * * 11-12 week 3: * * 3-20 * *4-2 * * 5-1 * * 6-19 * *7-18 * *8-17 * *9-16 * *10-15 11-14 * * * 12-13 week 4: * * 4-20 * *5-3 * * 6-2 * * 7-1 * * 8-19 * *9-18 * *10-17 * 11-16 12-15 * * * 13-14 week 5: * * 5-20 * *6-4 * * 7-3 * * 8-2 * * 9-1 * * 10-19 * 11-18 * 12-17 13-16 * * * 14-15 week 6: * * 6-20 * *7-5 * * 8-4 * * 9-3 * * 10-2 * *11-1 * *12-19 * 13-18 14-17 * * * 15-16 week 7: * * 7-20 * *8-6 * * 9-5 * * 10-4 * *11-3 * *12-2 * *13-1 * *14-19 15-18 * * * 16-17 week 8: * * 8-20 * *9-7 * * 10-6 * *11-5 * *12-4 * *13-3 * *14-2 * *15-1 16-19 * * * 17-18 week 9: * * 9-20 * *10-8 * *11-7 * *12-6 * *13-5 * *14-4 * *15-3 * *16-2 17-1 * * * *18-19 week 10: * *10-20 * 11-9 * *12-8 * *13-7 * *14-6 * *15-5 * *16-4 17-3 * * * *18-2 * *19-1 week 11: * *11-20 * 12-10 * 13-9 * *14-8 * *15-7 * *16-6 * *17-5 18-4 * * * *19-3 * *1-2 week 12: * *12-20 * 13-11 * 14-10 * 15-9 * *16-8 * *17-7 * *18-6 19-5 * * * *1-4 * * 2-3 week 13: * *13-20 * 14-12 * 15-11 * 16-10 * 17-9 * *18-8 * *19-7 1-6 2-5 * * 3-4 week 14: * *14-20 * 15-13 * 16-12 * 17-11 * 18-10 * 19-9 * *1-8 2-7 3-6 * * 4-5 week 15: * *15-20 * 16-14 * 17-13 * 18-12 * 19-11 * 1-10 * *2-9 3-8 4-7 * * 5-6 week 16: * *16-20 * 17-15 * 18-14 * 19-13 * 1-12 * *2-11 * *3-10 4-9 5-8 * * 6-7 week 17: * *17-20 * 18-16 * 19-15 * 1-14 * *2-13 * *3-12 * *4-11 5-10 * * * *6-9 * * 7-8 week 18: * *18-20 * 19-17 * 1-16 * *2-15 * *3-14 * *4-13 * *5-12 6-11 * * * *7-10 * *8-9 week 19: * *19-20 * 1-18 * *2-17 * *3-16 * *4-15 * *5-14 * *6-13 7-12 * * * *8-11 * *9-10 Same thing in a more condensed format to (hopefully) avoid line length problems week 1: 1-20, 2-19, 3-18, 4-17, 5-16, 6-15, 7-14, 8-13, 9-12, 10-11 week 2: 2-20, 3-1, 4-19, 5-18, 6-17, 7-16, 8-15, 9-14, 10-13, 11-12 week 3: 3-20, 4-2, 5-1, 6-19, 7-18, 8-17, 9-16, 10-15, 11-14, 12-13 week 4: 4-20, 5-3, 6-2, 7-1, 8-19, 9-18, 10-17, 11-16, 12-15, 13-14 week 5: 5-20, 6-4, 7-3, 8-2, 9-1, 10-19, 11-18, 12-17, 13-16, 14-15 week 6: 6-20, 7-5, 8-4, 9-3, 10-2, 11-1, 12-19, 13-18, 14-17, 15-16 week 7: 7-20, 8-6, 9-5, 10-4, 11-3, 12-2, 13-1, 14-19, 15-18, 16-17 week 8: 8-20, 9-7, 10-6, 11-5, 12-4, 13-3, 14-2, 15-1, 16-19, 17-18 week 9: 9-20, 10-8, 11-7, 12-6, 13-5, 14-4, 15-3, 16-2, 17-1, 18-19 week 10: 10-20, 11-9, 12-8, 13-7, 14-6, 15-5, 16-4, 17-3, 18-2, 19-1 week 11: 11-20, 12-10, 13-9, 14-8, 15-7, 16-6, 17-5, 18-4, 19-3, 1-2 week 12: 12-20, 13-11, 14-10, 15-9, 16-8, 17-7, 18-6, 19-5, 1-4, 2-3 week 13: 13-20, 14-12, 15-11, 16-10, 17-9, 18-8, 19-7, 1-6, 2-5, 3-4 week 14: 14-20, 15-13, 16-12, 17-11, 18-10, 19-9, 1-8, 2-7, 3-6, 4-5 week 15: 15-20, 16-14, 17-13, 18-12, 19-11, 1-10, 2-9, 3-8, 4-7, 5-6 week 16: 16-20, 17-15, 18-14, 19-13, 1-12, 2-11, 3-10, 4-9, 5-8, 6-7 week 17: 17-20, 18-16, 19-15, 1-14, 2-13, 3-12, 4-11, 5-10, 6-9, 7-8 week 18: 18-20, 19-17, 1-16, 2-15, 3-14, 4-13, 5-12, 6-11, 7-10, 8-9 week 19: 19-20, 1-18, 2-17, 3-16, 4-15, 5-14, 6-13, 7-12, 8-11, 9-10 This is an example of a Round robin algorithm, see http://www.devenezia.com/downloads/r...bin/index.html The formula in cell B2 is this: =1+MOD(ROW()+COLUMN()-4,19)&"-"&IF(COLUMN()=2,20,1+MOD(ROW()-COLUMN(),19)) Copy to the right until cell K2 and then down to row 20 Hope this helps / Lars-Åke To make this a bit more general. Put the following formula in cell B2 and copy it right and down as far as needed. (The number of columns should be half of the maximum number of players, and the number of rows should be equal to the maximum number of players minus one). =IF(OR(ROW()CEILING($A$1,2),COLUMN()*2CEILING($A $1,2)+2),"", 1+MOD(ROW()+COLUMN()-4,CEILING($A$1,2)-1)&"-"&IF(COLUMN()=2, CEILING($A$1,2),1+MOD(ROW()-COLUMN(),CEILING($A$1,2)-1))) Then put the number of players in cell A1 and watch the pairings for different number of players. If the number of players are odd, the highest number should be considered as a "ghost player" and matches with the "ghost" player should be ignored. If you want to replace the numbers with player names, try this formula: =IF(OR(ROW()CEILING($A$1,2),COLUMN()*2CEILING($A $1,2)+2),"", INDEX(Players,1+MOD(ROW()+COLUMN()-4,CEILING($A$1,2)-1))&"-"& INDEX(Players,IF(COLUMN()=2,CEILING($A$1,2),1+MOD( ROW()-COLUMN(),CEILING($A*$1,2)-1)))) where "Players" is a named range with the names of all players. Hope this helps / Lars-Åke- Hide quoted text - - Show quoted text - Hi Lars, Both of the first two algorithms worked fantastic, especially where the number of players is defined. It then allows for more than 20 players! However, algorithm 3 didn't work... with or without named range for players... cheers Pete |
League Fixtures
On Mon, 27 Jul 2009 15:24:31 -0700 (PDT), DubboPete
wrote: On Jul 28, 1:25*am, Lars-Åke Aspelin wrote: On Mon, 27 Jul 2009 14:29:58 GMT, Lars-Åke Aspelin wrote: On Mon, 27 Jul 2009 03:34:59 -0700 (PDT),DubboPete wrote: Hi all, I have been racking my brains for the last three days trying to work a logical sequence to setting up fixtures for a 19 week league competition for 20 players. Here's what I want to do: 20 players (numbered 1 thru 20 for ease of use) each play a game per week, so there's 10 games per week. * Each player only plays an opponent once every 19 weeks. * Each player obviously plays 19 games - one player plays 19 opponents. I just can't work out a formula to set the fixtures automatically without any duplications. * Can anyone help or point me in the right direction? cheers in anticipation DubboPete Here is one example of pairing of players for the 19 weeks. week no * * pair 1 *pair 2 *pair 3 *pair 4 *pair 5 *pair 6 *pair 7 *pair 8 pair 9 * * *pair 10 week 1: * * 1-20 * *2-19 * *3-18 * *4-17 * *5-16 * *6-15 * *7-14 * *8-13 9-12 * * * *10-11 week 2: * * 2-20 * *3-1 * * 4-19 * *5-18 * *6-17 * *7-16 * *8-15 * *9-14 10-13 * * * 11-12 week 3: * * 3-20 * *4-2 * * 5-1 * * 6-19 * *7-18 * *8-17 * *9-16 * *10-15 11-14 * * * 12-13 week 4: * * 4-20 * *5-3 * * 6-2 * * 7-1 * * 8-19 * *9-18 * *10-17 * 11-16 12-15 * * * 13-14 week 5: * * 5-20 * *6-4 * * 7-3 * * 8-2 * * 9-1 * * 10-19 * 11-18 * 12-17 13-16 * * * 14-15 week 6: * * 6-20 * *7-5 * * 8-4 * * 9-3 * * 10-2 * *11-1 * *12-19 * 13-18 14-17 * * * 15-16 week 7: * * 7-20 * *8-6 * * 9-5 * * 10-4 * *11-3 * *12-2 * *13-1 * *14-19 15-18 * * * 16-17 week 8: * * 8-20 * *9-7 * * 10-6 * *11-5 * *12-4 * *13-3 * *14-2 * *15-1 16-19 * * * 17-18 week 9: * * 9-20 * *10-8 * *11-7 * *12-6 * *13-5 * *14-4 * *15-3 * *16-2 17-1 * * * *18-19 week 10: * *10-20 * 11-9 * *12-8 * *13-7 * *14-6 * *15-5 * *16-4 17-3 * * * *18-2 * *19-1 week 11: * *11-20 * 12-10 * 13-9 * *14-8 * *15-7 * *16-6 * *17-5 18-4 * * * *19-3 * *1-2 week 12: * *12-20 * 13-11 * 14-10 * 15-9 * *16-8 * *17-7 * *18-6 19-5 * * * *1-4 * * 2-3 week 13: * *13-20 * 14-12 * 15-11 * 16-10 * 17-9 * *18-8 * *19-7 1-6 2-5 * * 3-4 week 14: * *14-20 * 15-13 * 16-12 * 17-11 * 18-10 * 19-9 * *1-8 2-7 3-6 * * 4-5 week 15: * *15-20 * 16-14 * 17-13 * 18-12 * 19-11 * 1-10 * *2-9 3-8 4-7 * * 5-6 week 16: * *16-20 * 17-15 * 18-14 * 19-13 * 1-12 * *2-11 * *3-10 4-9 5-8 * * 6-7 week 17: * *17-20 * 18-16 * 19-15 * 1-14 * *2-13 * *3-12 * *4-11 5-10 * * * *6-9 * * 7-8 week 18: * *18-20 * 19-17 * 1-16 * *2-15 * *3-14 * *4-13 * *5-12 6-11 * * * *7-10 * *8-9 week 19: * *19-20 * 1-18 * *2-17 * *3-16 * *4-15 * *5-14 * *6-13 7-12 * * * *8-11 * *9-10 Same thing in a more condensed format to (hopefully) avoid line length problems week 1: 1-20, 2-19, 3-18, 4-17, 5-16, 6-15, 7-14, 8-13, 9-12, 10-11 week 2: 2-20, 3-1, 4-19, 5-18, 6-17, 7-16, 8-15, 9-14, 10-13, 11-12 week 3: 3-20, 4-2, 5-1, 6-19, 7-18, 8-17, 9-16, 10-15, 11-14, 12-13 week 4: 4-20, 5-3, 6-2, 7-1, 8-19, 9-18, 10-17, 11-16, 12-15, 13-14 week 5: 5-20, 6-4, 7-3, 8-2, 9-1, 10-19, 11-18, 12-17, 13-16, 14-15 week 6: 6-20, 7-5, 8-4, 9-3, 10-2, 11-1, 12-19, 13-18, 14-17, 15-16 week 7: 7-20, 8-6, 9-5, 10-4, 11-3, 12-2, 13-1, 14-19, 15-18, 16-17 week 8: 8-20, 9-7, 10-6, 11-5, 12-4, 13-3, 14-2, 15-1, 16-19, 17-18 week 9: 9-20, 10-8, 11-7, 12-6, 13-5, 14-4, 15-3, 16-2, 17-1, 18-19 week 10: 10-20, 11-9, 12-8, 13-7, 14-6, 15-5, 16-4, 17-3, 18-2, 19-1 week 11: 11-20, 12-10, 13-9, 14-8, 15-7, 16-6, 17-5, 18-4, 19-3, 1-2 week 12: 12-20, 13-11, 14-10, 15-9, 16-8, 17-7, 18-6, 19-5, 1-4, 2-3 week 13: 13-20, 14-12, 15-11, 16-10, 17-9, 18-8, 19-7, 1-6, 2-5, 3-4 week 14: 14-20, 15-13, 16-12, 17-11, 18-10, 19-9, 1-8, 2-7, 3-6, 4-5 week 15: 15-20, 16-14, 17-13, 18-12, 19-11, 1-10, 2-9, 3-8, 4-7, 5-6 week 16: 16-20, 17-15, 18-14, 19-13, 1-12, 2-11, 3-10, 4-9, 5-8, 6-7 week 17: 17-20, 18-16, 19-15, 1-14, 2-13, 3-12, 4-11, 5-10, 6-9, 7-8 week 18: 18-20, 19-17, 1-16, 2-15, 3-14, 4-13, 5-12, 6-11, 7-10, 8-9 week 19: 19-20, 1-18, 2-17, 3-16, 4-15, 5-14, 6-13, 7-12, 8-11, 9-10 This is an example of a Round robin algorithm, see http://www.devenezia.com/downloads/r...bin/index.html The formula in cell B2 is this: =1+MOD(ROW()+COLUMN()-4,19)&"-"&IF(COLUMN()=2,20,1+MOD(ROW()-COLUMN(),19)) Copy to the right until cell K2 and then down to row 20 Hope this helps / Lars-Åke To make this a bit more general. Put the following formula in cell B2 and copy it right and down as far as needed. (The number of columns should be half of the maximum number of players, and the number of rows should be equal to the maximum number of players minus one). =IF(OR(ROW()CEILING($A$1,2),COLUMN()*2CEILING($A $1,2)+2),"", 1+MOD(ROW()+COLUMN()-4,CEILING($A$1,2)-1)&"-"&IF(COLUMN()=2, CEILING($A$1,2),1+MOD(ROW()-COLUMN(),CEILING($A$1,2)-1))) Then put the number of players in cell A1 and watch the pairings for different number of players. If the number of players are odd, the highest number should be considered as a "ghost player" and matches with the "ghost" player should be ignored. If you want to replace the numbers with player names, try this formula: =IF(OR(ROW()CEILING($A$1,2),COLUMN()*2CEILING($A $1,2)+2),"", INDEX(Players,1+MOD(ROW()+COLUMN()-4,CEILING($A$1,2)-1))&"-"& INDEX(Players,IF(COLUMN()=2,CEILING($A$1,2),1+MOD( ROW()-COLUMN(),CEILING($A*$1,2)-1)))) where "Players" is a named range with the names of all players. Hope this helps / Lars-Åke- Hide quoted text - - Show quoted text - Hi Lars, Both of the first two algorithms worked fantastic, especially where the number of players is defined. It then allows for more than 20 players! However, algorithm 3 didn't work... with or without named range for players... cheers Pete Could you please expand a bit on "didn't work". Was it possible to enter the formula at all or did you get some error? If formula was entered, what was the result you got? Here is what I got when I used the formula with number of players = 8. Alfa-Hotel Bravo-Golf Charlie-Foxtrot Delta-Echo Bravo-Hotel Charlie-Alfa Delta-Golf Echo-Foxtrot Charlie-Hotel Delta-Bravo Echo-Alfa Foxtrot-Golf Delta-Hotel Echo-Charlie Foxtrot-Bravo Golf-Alfa Echo-Hotel Foxtrot-Delta Golf-Charlie Alfa-Bravo Foxtrot-Hotel Golf-Echo Alfa-Delta Bravo-Charlie Golf-Hotel Alfa-Foxtrot Bravo-Echo Charlie-Delta Do you get correct results if you test your named range with the following formula? =INDEX(Players,1) =INDEX(Players,2) etc Lars-Åke |
| All times are GMT +1. The time now is 07:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com