soccer tournament template

[shagil]

i have 4 teams. we play a league (three rounds). the bottom on the ranking is
eliminated and will not make it to the semi final. Inthe semi final, team
with the highest in rank plays the second highest. Winner goes to the final,
Looser plays the secon semi final match with the third team in rank. so the
winner in this match goes to the final as the second finalist. Winner in this
match gets th cup. I tried to use the =VLOOKUP($--,$--$--$--,--,FALSE) for
automatic rank but its not working. any help is appreciated. thanks

 

[Pete_UK]

Although much larger than your application, have a look here for a
working example of a soccer league:

http://www.xldynamic.com/source/xld.LeagueTable.html

This should give you some ideas.

Hope this helps.

Pete

 

[Peo Sjoblom]

I never thought I'd see the day when someone with UK in his name would call
football soccer

 

[Pete_UK]

I used to call it soccer when I was a kid, many years ago - some of my
friends thought football meant Rugby Football !! <bg>

Pete

 

[shagil]

We call the game 'football' also in this part of the world where i live but i
used "soccer" (stupid as it sounds) in order to prevent any confusion.
anyway thank you for replying.
i did try the template you mentioned but it is not axactly what i need.
I was thinking of the all famous world cup score chart where i would have
the tournament game schedule with a table (group rankings) on the right side
.. as soon as the match scores have been entered, ranking must update
automatically with the team with most points in the 1st row of the ranking. I
can mail what i hve done with the excel sheet if need be. I hope i am making
sense.

 

[Pete_UK]

The example I pointed you to has 20 teams in a single league - in the
World Cup there are usually 8 leagues of 4 teams, so I thought you
could cut the example down to 4 teams and then just replicate it 7
more times. That would give you the initial group stages, but the
tournament then goes into a knockout phase, so a different approach
would be needed for that.

Whenever the World Cup (or European Championship) is on, there are
usually templates printed in newspapers showing all the games
scheduled, so I would suggest that you base your worksheet(s) on one
of those.

If you take the Rugby World Cup, currently on in France, there are 4
leagues with about 6 teams in each league for the initial stages, so
you might want to be a bit more flexible in your design to cope with
situations like that.

Hope this helps.

Pete

 

[shagil]

thank you. i managed to create just the type of template I was refering to.
Now my problem is this;

in the rank table i have 9 columns (Rank, Team, W, D, L, GF, GA, GD,
Points). i inserted 2 three more columns next to "points" colmn for
application of goal difference in case of ties with the headings "rank"
(=RANK(R5,$R$4:$R$8), "rankGF"
(=SUMPRODUCT((R5=$R$4:$R$8)*(O5<$O$4:$O$8))),"Rank GD"
(=SUMPRODUCT((R5=$R$4:$R$8)*(P5<$P$4:$P$8))).... in the first column where i
hadd the original "rank heading I inserted the formula
(=RANK(R5,$R$4:$R$8)+SUMPRODUCT((R5=$R$4:$R$8)*(O5<$O$4:$O$8))+SUMPRODUCT((S5=$S$4:$S$8)*(T5=$T$4:$T$8)*(U5>$U$4:$U$4))

now everything works fine. but i needed the team with most points to
actually be on the first row, second team in the second row etc....
so i created a second table where on the first column i inserted "rank" n
wrote the rank 1-4 in the rows below. in the column I had the heading "W". I
inserted the formula (=VLOOKUP($J12,$J$5:$U$8,2,FALSE) for the next column
and just changed the 2 to 3. It now updated just as i wanted. but this
formular did not work when i reached the GF,GA and GD columns, i got the
error msg that excel cannot calculate a formular. i am stuck at this point
now guys. if you can help me sove this i have my template.

 

[Pete_UK]

You can send the file to me:

pashurst <at> auditel.net

Change the obvious.

Note that it is 12:35am here in the UK, so I shall look at it tomorrow
unless you can send it fairly quickly.

Pete