Matching.....

[Noob Jedi]

Let's say you have a list of 20 teams. And there is a schedule that
includes the total number of 50 teams total to play each other. Not
all teams will play each other. Within that list of 20, how do I go
about setting up the spreadsheet to count how many times a team in
that list of 20 will play another team in that same list? I hope I'm
asking this with enough clarity.

 

[Pete_UK]

Do you mean that this is a knockout competition with 50 teams starting
but only the winners of games in each round progress to the next
round? Otherwise, I don't see how not all teams will play each other,
because in a league that is what happens, and you may have home and
away fixtures so teams play each other twice.

One way of representing the league-type situation is to arrange the
list of teams vertically in one column (these are the home teams) and
the same list of teams is arranged horizontally in the row above,
starting in the next column, and this represents the away teams.
Obviously, team1 cannot play itself, nor can team2, team3 etc, so the
leading (main) diagonal is void, but any other cell in the grid
represents a match between the vertical team (home) and the horizontal
team (away). The cells could contain a date (for the fixture), or a
result if the match has already taken place, or a simple Y/N to show
if the game has been played.

In a straight knockout competition each round represents a maximum of
a power of 2 teams playing each other, i.e. in the final there are two
teams, in the semi-final there are 4, in the quarter finals 8, in the
previous round 16, then 32, 64, 128 etc in the earlier rounds. In your
case, with 50 teams, for the first round you would have to allow 14
teams to have byes (progess directly into the next round), so that the
remaining 36 teams will yield 18 winners to give 32 teams in the next
round. Thus in 1 column you would have up to 64 entries, then 32 in
the next, 16 in the next, and so on.

Of course, there are some competitions with a mixture of mini-league
arrangements for the early rounds, and then winners of these leagues
progress into the knockout phase.

I'm sure you know all this, so can you explain in a bit more detail
exactly what you have and what you want out of it?

Pete

 

[Noob Jedi]

Do you mean that this is a knockout competition with 50 teams starting
but only the winners of games in each round progress to the next
round? Otherwise, I don't see how not all teams will play each other,
because in a league that is what happens, and you may have home and
away fixtures so teams play each other twice.

One way of representing the league-type situation is to arrange the
list of teams vertically in one column (these are the home teams) and
the same list of teams is arranged horizontally in the row above,
starting in the next column, and this represents the away teams.
Obviously, team1 cannot play itself, nor can team2, team3 etc, so the
leading (main) diagonal is void, but any other cell in the grid
represents a match between the vertical team (home) and the horizontal
team (away). The cells could contain a date (for the fixture), or a
result if the match has already taken place, or a simple Y/N to show
if the game has been played.

In a straight knockout competition each round represents a maximum of
a power of 2 teams playing each other, i.e. in the final there are two
teams, in the semi-final there are 4, in the quarter finals 8, in the
previous round 16, then 32, 64, 128 etc in the earlier rounds. In your
case, with 50 teams, for the first round you would have to allow 14
teams to have byes (progess directly into the next round), so that the
remaining 36 teams will yield 18 winners to give 32 teams in the next
round. Thus in 1 column you would have up to 64 entries, then 32 in
the next, 16 in the next, and so on.

Of course, there are some competitions with a mixture of mini-league
arrangements for the early rounds, and then winners of these leagues
progress into the knockout phase.

I'm sure you know all this, so can you explain in a bit more detail
exactly what you have and what you want out of it?

Pete



- Show quoted text -

Well, I was using 50 and 20 as a sample number and not really focusing
on the math part. I thought about using your approach of vertical and
horizontal lines to represent when they play each other. This is
actually an addition to the previous question that you helped me with
me before. Basically, with that same list of predictions that I had
before, I want to see how many occurences of a team in that list plays
another team in that list exists. So if Team 4 and Team 9 are chosen,
and they are on the schedule to play each other at one point in time,
then that counts as one occurence. Now if Team 13 is chosen but does
not play any other team on that that predictions list, then no
occurence has occured, so it's not counted. I'm trying to see how I
can set up the spreadsheet to return these numbers.

 

[Pete_UK]

Yes, but what does your schedule look like? Is it:

teamA teamB date
teamM teamH date
teamF teamC date

and so on...? Or is it:

date teamA teamB
date teamM teamH
date teamF teamC

or even some other format?

Is this list in another sheet of the same file, or if it is on the
same sheet then what columns does it occupy and starting from which
cell?

Please give exact details of what you have.

Pete

 

[Noob Jedi]

Yes, but what does your schedule look like? Is it:

teamA teamB date
teamM teamH date
teamF teamC date

and so on...? Or is it:

date teamA teamB
date teamM teamH
date teamF teamC

or even some other format?

Is this list in another sheet of the same file, or if it is on the
same sheet then what columns does it occupy and starting from which
cell?

Please give exact details of what you have.

Pete





- Show quoted text -

Well, I can format it either way in your scenario. The original
schedule in the spreadsheet has the date as the top row. The rows
below, list out all possible teams. Respectively to each date per
column, it lists the teams the teams listed by row will play.

E.G.
B1:Z1 lists the dates assuming there are 26 teams
A2:A26 lists all possible teams...again assuming there are 26 teams
B2:Z2 Lists all the teams A2 will play respectively to the dates on
the top row.

Although, I think the stronger setup might be your second suggestion
as I tried it that way myself.

Once I get this setup, based on that predictions list again, I want to
it to automate how many times an occurence of a team playing each
other and both teams exist on that predictions list, which I'm sure
already know is my goal.

 

[Noob Jedi]

Well, I can format it either way in your scenario. The original
schedule in the spreadsheet has the date as the top row. The rows
below, list out all possible teams. Respectively to each date per
column, it lists the teams the teams listed by row will play.

E.G.
B1:Z1 lists the dates assuming there are 26 teams
A2:A26 lists all possible teams...again assuming there are 26 teams
B2:Z2 Lists all the teams A2 will play respectively to the dates on
the top row.

Although, I think the stronger setup might be your second suggestion
as I tried it that way myself.

Once I get this setup, based on that predictions list again, I want to
it to automate how many times an occurence of a team playing each
other and both teams exist on that predictions list, which I'm sure
already know is my goal.- Hide quoted text -

- Show quoted text -

I forgot to mention the schedule is in a different list, but can be
copied to my existing ranking sheet.

 

[Pete_UK]

I'm not sure where the schedule comes into this. Imagine you had three
teams and they play each other home and away. Then the games can be
considered as:

team1 plays team2
team1 plays team3
team2 plays team1
team2 plays team3
team3 plays team1
team3 plays team2

where the first mentioned team is the home team. You can see that
there are 6 games. Looked at another way, each of the three teams
playing at home would play the other teams (in this case 2), so the
number of games will be 3 * 2.

If you have 4 teams, each of the four teams would have 3 home matches,
so there will be 12 games. With 5 teams, each team would have 4 home
matches, giving 20 games.

In each case the number of games is the number of teams times the
number of other teams, or n * (n-1), where n is the number of teams.

What has the fixture list got to do with it?

In Excel terms, if the list of teams is in column A, then this formula
will give the number of games between them, home and away:

=COUNTA(A1:A100)*(COUNTA(A1:A100)-1)

assuming you have less than 100 teams - adjust the range to suit.

Hope this helps.

Pete

 

[Noob Jedi]

I'm not sure where the schedule comes into this. Imagine you had three
teams and they play each other home and away. Then the games can be
considered as:

team1 plays team2
team1 plays team3
team2 plays team1
team2 plays team3
team3 plays team1
team3 plays team2

where the first mentioned team is the home team. You can see that
there are 6 games. Looked at another way, each of the three teams
playing at home would play the other teams (in this case 2), so the
number of games will be 3 * 2.

If you have 4 teams, each of the four teams would have 3 home matches,
so there will be 12 games. With 5 teams, each team would have 4 home
matches, giving 20 games.

In each case the number of games is the number of teams times the
number of other teams, or n * (n-1), where n is the number of teams.

What has the fixture list got to do with it?

In Excel terms, if the list of teams is in column A, then this formula
will give the number of games between them, home and away:

=COUNTA(A1:A100)*(COUNTA(A1:A100)-1)

assuming you have less than 100 teams - adjust the range to suit.

Hope this helps.

Pete





- Show quoted text -

Mathmatically speaking, you are dead on. However, that would work if I
was looking for a mathmatical outcome of the total games played
amongst all teams.

What I'm looking for is if there are say 60 teams and your list only
has 20. Assuming eliminations exist, some teams will have never played
each other in the league. If the 15th team in your list plays the 17th
team on your list, that is an occurence. But if the 15th team plays a
team 21st to 60 (NOT on your list, that occurence is NOT counted). The
schedule is important because there are only slots for about 13-15
different days for games to be hosted.

The only approach I can think of is to have your original list. Going
through it one by one and checking their schedule to see if they play
anyone else on that same list. I don't care if they've played the
bottom 40 at all or not. I only care if 2 teams in your list of 20 at
some point play each other based on their schedule and how many times
that happens.

I guess it's starting to seem like I'm asking for too much of excel.
That or I don't know how to word what I want correctly.

 

[Pete_UK]

It's becoming clearer to me what you want, but I think you will need a
macro to achieve that - I've tried a SUMPRODUCT formula but couldn't
get it to work because there are dissimilar size arrays. Unfortunately
I can't help you anymore, as I'm going on holiday tomorrow and it is
past midnight here. Perhaps if you re-post in the programming
newsgroup you might be able to get more help.

I would suggest that you describe accurately what you have and what
you would like to achieve, i.e. you have a sub-list of teams in A1 to
A20 (out of a possible 50 teams) (Give the actual cell references) and
a schedule in columns F G and H (for example) which lists teams in G
playing other teams in H with the date of the game in F (again, give
actual cell references). As it is an elimination competition with
several stages (not a straight knock-out), not all teams will play
each other. You would like to be able to obtain a count of how many
teams from your sub-list in column A will actually play each other in
the schedule.

Hope this helps, and good luck.

Pete

 

[Noob Jedi]

It's becoming clearer to me what you want, but I think you will need a
macro to achieve that - I've tried a SUMPRODUCT formula but couldn't
get it to work because there are dissimilar size arrays. Unfortunately
I can't help you anymore, as I'm going on holiday tomorrow and it is
past midnight here. Perhaps if you re-post in the programming
newsgroup you might be able to get more help.

I would suggest that you describe accurately what you have and what
you would like to achieve, i.e. you have a sub-list of teams in A1 to
A20 (out of a possible 50 teams) (Give the actual cell references) and
a schedule in columns F G and H (for example) which lists teams in G
playing other teams in H with the date of the game in F (again, give
actual cell references). As it is an elimination competition with
several stages (not a straight knock-out), not all teams will play
each other. You would like to be able to obtain a count of how many
teams from your sub-list in column A will actually play each other in
the schedule.

Hope this helps, and good luck.

Pete








- Show quoted text -

Thanks for being so patient with me, Pete. You've been a tremendous
help. Have a good vacation.