Note 28-2-2014
I updated the Excel (see downloads) and this post, see part 3.
Part 1/3 (26-2-2014)
Toyday my last post dedicated to the Wintergames in Sochi 2014. This time I made an Excel (see fig.1) with the results (rankings) of all Dutch long-track skaters (so I excluded the short-track skaters and the team pursuit) of TeamNL. You did a great job at these Games and I congratulate all of you with your results, whether it was gold, silver bronze or a new PR.
To collect all rankings, I used this (Dutch) website:
http://www.schaatsen.nl/sotsji-2014/langebaan
which links to this one:
http://live.isuresults.eu/2013-2014/sochi
and if you click in the ranking-table on the name of a skater, it opens this website (and shows details about the skater).
To highlight the medal-winners, I color-coded rankings 1, 2 and 3 (green, orange, red), using Conditional Formatting. The tables with the medal-totals are calculated with the COUNTIF-formula, see e.g. :
http://www.techonthenet.com/excel/formulas/countif.php
So the Netherlands won 21 medals in longtrack (individual) skating, and 3 more in other disciples, which is a new record. Of course, every person who became a olympic champion has delivered a great performance and deserved to get the Dutch 'knighthood' title, which the Dutch king gave to them the other day (see fig.2), but in case the king could only give one title, I´d say to him to give it to Stefan Groothuis, who after having overcome a very difficult time and a bad start in Sochi (he fell in the 500m), won the 1000m, very impressive!
Fig.2: Dutch Skaters with King Willem Alexander and Queen Maxima,
source:
Today someone asked me about the order of the skaters in the ranking-table. The order doesn´t mean anything, it´s just how the skaters were ordered on this (Dutch) website::
http://www.schaatsen.nl/sotsji-2014/langebaan.
But the question gave me an idea: to order the skaters based on the number of medals they won, in the same way as the countries were ordered at the end of the Games (with the Netherlands at 5th place), see:
http://www.sochi2014.com/en/medals
So: nr.1 is the one who won the most gold medals, and if this is equal for 2 countries/skaters you look who won the the most silver medeals and if this is also equal you look who won the most bronze medals.
So I made a new table in my Excel (sheet 2, see fig.3), copying the table of sheet 1, but just the part with the skaters and their medal-totals for gold/silver/bronze, and then I did a multi-column ordering on this table, see:
http://office.microsoft.com/en-us/excel-help/sort-a-table-HA103993978.aspx#_Toc354843384
To get the ranking-nr (1st column), I added 4 columns (purple) to calculate the difference in the gold/silver/bronze total with the previous skater in the ranking-table, and if this is 0 for all 3 columns, it means the skater has the same ranking as the previous skater.To do this, I used a conditional formula for the 4th purple column:
Ranking_Diff =IF(AND([@[Gold_Diff]]=0;AND([@[Silver_Diff]]=0;[@[Bronze_Diff]]=0));0;1)
see:
http://office.microsoft.com/en-us/excel-help/create-conditional-formulas-HP005251012.aspx
As you can see in fig.3, Ireen Wüst was the most succesfull Dutch skater (and as I said earlier, my Excel only includes the individual long-track competitions (so I excluded short-track and team pursuit, and in the last, Wüst won another gold medal). After these Games, Wüst is the most succesfull Dutch olympic athlete (so of all olympic sports) ever, see this (Dutch) website::
http://nos.nl/os2014/artikel/614977-wust-beste-nederlandse-olympier.html
And she can even improve her 'score' because she said she will be there at the next Wintergames..
Part 3/3 (28-2-2014)
Besides several skaters, also 2 trainers were given the knighthood title, Gerard Kemkers (of team TVM) and Jac Orie (of team BrandLoyalty/Activia), and they deserve it.
NB: Most of the Dutch skaters train in professional teams, which have the name of their sponsor, only some skaters are independant, so without a team. For more details about the Dutch skaters and teams, see this (Dutch) website:
http://nos.nl/artikel/564722-overzicht-schaatsploegen-20132014.html
I wanted to compare the results of the teams (coaches), so I added to the table in worksheet 2 a colum 'team' (team-ID I should say, no team-name, to save space in the table) and I created a new table with the teams in which I count the total of gold/silver/medals (in the same way as I did for the skaters) with a formula like this (for the total of golden medals for team TVM):
=IF(D4="TVM";E4;0) + IF(D5="TVM";E5;0) + IF(D6="TVM";E6;0) + IF(D7="TVM";E7;0) + IF(D8="TVM";E8;0) + IF(D9="TVM";E9;0) + IF(D10="TVM";E10;0) + IF(D11="TVM";E11;0) + IF(D12="TVM";E12;0) + IF(D13="TVM";E13;0) + IF(D14="TVM";E14;0) + IF(D15="TVM";E15;0) + IF(D16="TVM";E16;0) + IF(D17="TVM";E17;0) + IF(D18="TVM";E18;0) + IF(D19="TVM";E19;0) + IF(D20="TVM";E20;0) + IF(D21="TVM";E21;0) + IF(D22="TVM";E22;0) + IF(D23="TVM";E23;0)
Maybe some of you think this is a very inefficient way to calculate the team-rankings if you compare it how you could do this in a relational database with count-queries, and this is true. Maybe one day I´ll dedicate a post to this topic, so relational databases.
Note 29-4-2014: Today I wrote this post about databases, see:
http://worktimesheet2014.blogspot.com.es/2014/04/excel-and-relational-databases.html
fig.3: Ranking Dutch skaters based on medals won at Olympic Wintergames 2014 Sochi
NB: independant skaters have value N/A for the team- and trainer-columns
Download-mirrors:
* Mirror #1:
Excel:
https://onedrive.live.com/redir?resid=3F963E9F1A42D952%21213
* Mirror #2:
https://drive.google.com/file/d/0BywxxSJoaUYxamc1QXRQbW12N1k/edit?usp=sharing
* Mirror #3:
PDF:
http://es.scribd.com/doc/209870540/Rankings-Dutch-Skaters-Wintergames2014-V3-P
* Mirror #3:
PDF:
http://es.scribd.com/doc/209870540/Rankings-Dutch-Skaters-Wintergames2014-V3-P
No comments:
Post a Comment