Prev: change text color when the value become lower than a certain value
Next: Macro Help
From: Colin on 4 May 2010 03:38
I have 20 teams who will each play 10 games against one of the other teams.
Can anyone please advise a formula that will match 10 pairs 5 times ensuring
that no team will play another team twice.

From: Pete_UK on 4 May 2010 05:14
Well, maybe not a single formula, but an approach you can take.

Set up a table with the list of teams in column A starting in A2.
Highlight A2:A20 and click <copy>, then move the cursor to B1 and use
Paste Special | Transpose to get the same list in the top row. This
table represents the fixtures, with teams from column A playing teams
in row 1. Usually HOME teams are in column A and AWAY teams in row 1,
but this may not apply if you are all playing at one venue.

Clearly, Team A cannot play itself, nor can Team B etc, so the leading
diagonal of this table is not used (marked with x below):

A B C D E F etc
A x
B x
C x
D x
E x
F x
etc

Then you can just fill in the blanks to represent games, ensuring that
no team plays more than 10 times - you can a COUNTIF formula in V2 to
count the number of matches on that row and for column B to get the
total matches for team A.

Hope this helps.

Pete



On May 4, 8:38 am, "Colin" <colin.go...(a)gmail.com> wrote:
> I have 20 teams who will each play 10 games against one of the other teams.
> Can anyone please advise a formula that will match 10 pairs 5 times ensuring
> that no team will play another team twice.

 | 
Pages: 1
Prev: change text color when the value become lower than a certain value
Next: Macro Help