In 2000 I wrote an Excel spreadsheet which I called the Euro 2000 Wallchart. I called it this because come every major tournament, everyone I knew would get their poster, or newspaper pullout and chart England’s (and other teams) performance throughout the competition.
There was nothing spectacularly original with this idea – I saw one done for World Cup 98. I made it better and look smarter. But there were a couple of flaws which I had fix along the way.
I made a conscious decision to do two things with the spreadsheet: I didn’t want it to contain macros, because they can be nasty and untrustworthy if you don’t know who’s sending it; and I wrote it in such a way that the curious could delve into it to see what I did. I’m not an Excel whizz. Another reason I didn’t put any macros in was because I’m rubbish at macros!
I never distributed beyond close friends, but these things end up flying around the world, and to this day I receive a steady trickle of email about it, asking for adaptations for their leagues etc.
I didn’t get around to producing a World Cup 2002 version, for reasons that I’ll come onto in a minute. But others did modify mine, leaving alone the properties page (which is the only page that gives an email address).
This year we’re on Euro 2004, and at least three different sets of people have adapted the sheet that I wrote for the new competition. But they’ve locked it up to some extent so that you either can’t enter details into the sheet beyond a certain point, or can’t see the formulas used. I’m beginning to get emails about this.
I’d love to help, but as I didn’t make the changes, I’m at a bit of a loss. I have used a password cracking macro (from here) to get into these sheets. Use the following depending on what version of the program you’re using.
1. If there are two small Euro logos at the top of the page, one over the dates and times of the first round matches, and one over Grupo A/Group A, then try using AAAAAAABABAw in Tools > Protection > Unlock Workbook.
2. If the map of Portugal is to the right of Group A and the spreadsheet is titled Euro 2004 Wallchart, then try using this password: AAAAAABBAAB7.
3. Another version is modified by “yakyak” and is unprotected by password. It has a larger Euro logo spanning the top of the sheet across from the first round games to group section.
In either case, you then should go to Tools > Options, select View, and check the Row and Column Headers, Horizontal Scroll bar, and Vertical Scroll bar, as well as Sheet Tabs.
If you then look at the columns, there’ll be a gap between at least two of the columns alphabetically. Highlight the columns either side of this gap (ie. Cols T:BV) by clicking on the letters themselves, right hand click, and choose Unhide. This should reveal the inner workings of the spreadsheet. There are three sets of three sorts, each changing the teams’ points, goal difference and goals for.
The reason for not putting together a new version of the spreadsheet was simple: I couldn’t get one working properly on the group round. If a group goes to a three or even four way tie, the mathematics and permutations of what’s involved get awfully complicated, with matches played between the tied teams going into their own mini-league. I have seen someone do this, but didn’t want to rip off their code.
In fact the workings that I employed are those used in traditional leagues, like England’s Premier League. It sorts teams first by points, then by goal difference, and then by goals scored.
But UEFA doesn’t use this system. UEFA’s rules are outlined here, but in summary if two or more teams finish level on points, they’re divided by looking at the results between those teams:
1. Points between the teams involved. (e.g. if two teams are on the same points, but one beat the other in the group round, that team goes higher)
2. Goal difference between the teams involved.
3. Goals scored between the teams involved.
4. Goals scored between the teams involved.
5. Goal difference in all games.
6. Goals scored in all games.
7. Coefficient points from World Cup and Euro competitions.
8. Fairplay so far (ie. Yellow cards etc)
9. Drawing of lots.
Oh and if the final group game between two teams ends in a draw and they have the same number of points, goals scored and goal difference, they ignore most of the above criteria and go to a penalty shoot out.
You try coding that lot into an Excel sheet!
I may still put together something for this year, but there’ll have to be some degree of originality involved before I do it. And, er, quite a lot of work!
Read on in the extended entry to learn more about the sort techniques involved to produce your own league table.
UPDATE: I will publish more about this at a later stage, but I’d recommend using the official UEFA spreadsheet which can be found here since it does follow all the competition’s rules regarding splitting teams. Once I’ve fully understood how it works, I’ll publish a full explanation.
The sort technique used in the worksheets that I’ve put together does not use macros, but will only work with a group of four or fewer teams. If you have larger groups, then you really need to use macros and either use the built in Excel Sort function.
There are several different sort algorithms, and at a later point, I’ll detail there some more in here, and hopefully supply some answers. Come back later, or leave me a comment.