It is not easy to write again after three months I didn’t write and post any templates. And, I am really sorry for not replying many comments due to my time limitation to monitor this site. But…, I am back now with my newest sport template, World Cup 2010 Schedule and Scoresheet Template. This template is the first version, because I plan to add analysis worksheet which can help you (and me of course :-)) analyzing your favourite teams before they play their matches.
This template is based on my previous Euro 2008 template. Because World Cup 2010 tournament consists of 32 teams, compare with Euro 2008 tournament which only consists of 16 teams, I made lay out modification to make the sheet fit nicely and properly if it is printed within one page A4 or letter paper size.
I am also adjusting the excel formula for deciding and ranking the teams that qualify to the next round after they played all their group matches, if two or more teams have same points, based on World Cup tournament tie regulation where the tie regulation is slightly different with Euro 2008 tie regulation.
I maintain the language and timezone conversion, so you can use or print this template based on your language and time zone. I would like to thanks to Google which provide a very good translation tool which translate my words in this template in seconds. But, I still need a better human translation to make it fully comply with your language. You can help me refining that translation, or add new language translation, by downloading the language file and send it back to me. I will put your name as credit in this site. There 34 different languages and 139 cities timezone available in this version. Just select your language and timezone from the dropdown list in the upper left side of the worksheet to change the appearance of the worksheet into your local language and timezone.
I add a new excel formula to dynamically manage the appearance of team or country flags in Group Standings Table. In the beginning, I plan to put that formula next to every teams in every matches. But, it makes the excel run very slow every time you change cells or fill the scores. So, I decided to drop the formula in any team matches and apply the formula in Group standing table only.
Now, let’s go to the detail on how to use this spreadsheet as your scoresheet. If you ever had download my sport templates, you will be familiar with my sport style template where there are score boxes with blue color which ready to be filled with your estimation score or real score results.And you don’t have to read this instruction. Just use it. For you that download this spreadsheet for the first time, you can read this explanation below carefully to understand how this spreadsheet works.
This scoresheet consists of two big parts, the group stage part and the knock out round part. In the group stage part, you should fill the blue color boxes in all 48 group matches with the real score results or your score prediction. You will see the group standing table next to group matches table are changing automatically based on your input.
After all teams playing their group matches, the excel formula will place the team which quality in first and second position of the group based on group standing regulation. If their points are not the same, it is easy to place the team in first and second position. And the challenge in creating the spreadsheet is in this part, where I have to create the correct formula that will accommodate all possible condition based on FIFA tie regulation. I quoted the group ranking regulation from FIFA World Cup document :
The ranking of each team in each group will be determined as follows:
a) greatest number of points obtained in all group matches;
b) goal difference in all group matches;
c) greatest number of goals scored in all group matches.
If two or more teams are equal on the basis of the above three criteria, their rankings will be determined as follows:
d) greatest number of points obtained in the group matches between the teams concerned;
e) goal difference resulting from the group matches between the teams concerned;
f) greater number of goals scored in all group matches between the teams concerned;
g) drawing of lots by the FIFA Organising Committee.
At least, this tie regulation is much more simpler than Champions League Group tie regulation. I put some samples of the implementation of group ranking regulation as you can see in the pictures. I will explain the ranking regulation applied in each group to give you better understanding on this group ranking and tie regulation.
Group A (Rule a) and b) applied)
After all teams played their matches, France is qualified without question based on rule a) and South Africa is qualified based on rule b) because South Africa has a better goal difference compare with Uruguay.
Group B (Rule a) and c) applied)
In Group B, Argentina and South Korea are having the same point. Because both teams have the highest points compare with other teams in Group B, both teams are qualified. And based on rule b), South Korea will get the top position because South Korea scored more goals, 7 goals, compare with Argentina, 6 goals.
Group C (Rule a) and b) applied)
In Group C, England and Slovenia are having the same point, six. Eventhough England beat Slovenia in their meeting, Slovenia will top the group because Slovenia has a better goal difference.
Group D (Rule b) and tie rule d) applied)
There are three teams that are having the same points. Based on rule b), Serbia will top the group. And because Ghana and Germany are having the same goal difference and the same goal scored, tie rule d) will make Ghana deserve the runner up position because Ghana beat Germany in their meeting.
Group E (Rule c) applied)
This group also has three teams that are sharing the same points. They also has the same goal difference. But, Japan scored more goals than Netherlands, and Netherlands scored more goals than Denmark. So, Denmark is eliminated based on rule c).
Group F (Rule a) and c) applied)
In this group, Italy is topping its group and Slovakia take the runner up position based on rule a) and c).
Group G (Rule a) and tie rule d) applied)
In Group G, Brazil is qualified perfectly by beating all its opponents. But, the others are having the same points (3). Based on rule c), North Korea and Cote-d’Ivoire will go into tie regulation to decide which team will advance to the next round. And tie rule d) reveal North Korea as a runner up because they beat Cote-d’Ivoire in their meeting.
Group H (Rule a) and tie rule g) applied)
This group will use tie rule g) to decide whether Chile or Switzerland that will accompany Spain advance to the next round. Both teams are having the same points, goal difference, goals scored, and also they are sharing the same point in their meeting. So, FIFA will draw them, and in this example, Chile is lucky. But, I haven’t finished to create a drawing table that can be manually filled. In this spreadsheet version, this ranking is still based on FIFA rank, used for group drawing. I plan to reveal a drawing table when some teams are going to be drawn and can be filled manually in the later version.
As additional information, this is the FIFA team ranking used as group drawing lots reference . I took the information from Wiki.
1. South Africa (hosts)
11. United States
17. Côte d’Ivoire
29. Korea Republic
31. New Zealand
32. Korea DPR
KNOCK OUT STAGES
When all group matches are finished and the team that qualified are revealed, the excel formula will place all teams in their corresponding match boxes based on FIFA rule. And if all first seeded are topping their group, you won’t see Brazil vs England in World Cup final :-).
In knock out stage, you have to fill the score in one or three blue blank boxes in every matches to reveal and put the winner to next round. If the match is ended in normal time, you just fill the first blue box and the final score will reveal next to each team name. If it has to go to extra time, put the tie score in the first blue box, and put the extra time score in the second blue box. If the match has to be decided by penalty shoot out, put the tie score in extra time box and put the penalty score in the last blue box to reveal the winner of the match. You can the sample in the picture below.
Do the same steps until the final match which will reveal the 2010 World Cup Champion. And before the champion is revealed, feel free to play with those score boxes.
I plan to release the next version which will have player information, match analysis based on player stats, team head-to-head statistic and also other layout types. I hope I can release that in May. And I will separate the templates into several files, so you can choose which template that suit your needs.
If you like my template and want to help other people around the world using this template, you can contribute to correct or add new translation by downloading my language spreadsheet. If the language is already available, you don’t have to write all the translation, just type the correct one next to the English language. I will put your name for your contribution in this site.
I have received several language files, and I would like to thanks these people below for sending me the correct translation, and will make other people who use this spreadsheet in their language will use it more human than more google … ;-). Those are: Jack Sakali (Arabic), Smartish (Chinese-Simplified), eLook (Chinese-Traditional), Michael Calundann (Danish), Helmer Peerdeman and Willem Norbruis (Dutch), Rauli Korpela (Finnish), Nishal Sooredoo (French), David Rapava (Georgian), Hans A. Keks (German/Deutsch), Achilleas Katsaros, Zach Ioannou and Stelios (Greek), Benysamo (Hebrew), Jozef Kele (Hungarian), Wahyudi Palwono, Herman Macci, Adjie Heryanto, Zainal Arifin and Ananda Raja (Indonesian), Sigursteinn Leifsson (Icelandic), Luca (Italian), Raizal Sohri, Hamal Anuar Md Ali and Nazre bin Abdul Rashid (Malay), Muzzy (Mongolian), Jostein R. Hareide and Kay Ronny Dahl (Norway), Amir Asadi and Amir Yarazavi (Persian), Wally K. and Lukas (Polish), Laercio Fernandes Neves (Portuguese-Brazil), Bruno Afonso, D. Tiago and António Macedo (Portuguese-Portugal), Tudor Apostolescu (Romanian), Kozlov Alexander (Russian), Marko Papic (Serbian), Martin Biros and Michal Zajaek (Slovak), Jorge Cantero Gómez and Jesus Duran (Spanish), Javier Mariano Areco (Spanish-Argentine), Peter Bacharach (Swedish), Win Limthanopajai and Somphop Muenpakdee (Thai), M.Volkan Çakr (Turkish), Mykola Volkogon (Ukrainian), Dung Le Trung, Le Vuong Anh, Philip Nguyen, Bch Tin, Pham Ly Thanh, Nguyen Viet Ha, Vit Thng, Bao Nguyen, Huy Bao Dao, and Tran Quoc Bao (Vietnamese). There are still some languages in process but I am still waiting other language correction to make all languages in this spreadsheet translated correctly.
There are also additional two spreadsheet with no nation flag which should solve compatibility problem in MS Excel 2010 and Openoffice and also for those who running Excel in Macintosh OS/Apple.
And also I would like to thanks Adrian Flores for informing me about flag mixed up, Josef for incorrect match number and also for Noah and Eric van Rooijen who correcting my rule missinterpretation. GBU all.
Here are all the files :
World Cup 2010 Schedule and Scoresheet V1.45 (197.6 KiB, 86,060 hits)
Contribute a better language translation (20.5 KiB, 9,236 hits)
World Cup 2010 Schedule and Scoresheet V1.45 without Nation Flag (127.3 KiB, 7,219 hits)
World Cup 2010 Schedule and Scoresheet V1.45 Sorted by Group Matches (127.6 KiB, 13,555 hits)