17 Feb 2014

Tracker for Speed Skating 10km Men Sochi 2014

#11  Tracker for Speed Skating 10km Men at Olympic Wintergames Sochi 2014

Note 19-2-2014:
I updated the Excel with the times of the best 3 skaters, see Download-mirrors: 'Race'. And I also uploaded a PDF-version (see mirror #3). I added an extra sheet to compare the results of the winner and
runner-up, with e.g. laptimes and speed and the difference with the previous lap, see fig.2, 3, so similar to
the Excel in my previous post about the 5km men, see:
For more details about fig.2-3, see 'Part 2' of this post.

Part 1/2 (18-2-2014, before the race)

Tomorrow will be the 10.000 m speed-skating competition for men in the Olympic Wintergames in Sochi.
I made an Excel for this event which you can use during the race. To do this, you must enter the (total/cumulative) laptimes in the (red) columns, in format: nn:ss.mmm (minute, sec., milli-sec, e.g. 10:11.710)
If you do this after every lap, you can see in the Excel the difference with the fastest skater in the competition and also the differences with the World Record (WR) and Olympic Record (OR). And in column 'S1/2/3 Diff. Fastest', the yellow cell is in the column of the fastest skater (1/2/3) until that lap.
In fig.1 you can see an example of the result, at the end of the competition, so with all laptimes for the 3 best skaters. To use this Excel for the real competition, you have to delete these laptimes (in the red-columns).
I uploaded 2 versions of this Excel on download-mirror #1, one read-only version with the demo-data
and one read/write version with empty lap-time columns, so if someone likes to fill in the lap-times
of the race 'live', that is posible (using the the cloud-version of Excel). Unfortunately I won´t have time to do it myself, why didn´t they schedule this competion later in the afternoon..

For those interested in who are the favourites, see:


So as you can see, if Sven Kramer won´t make any mistakes (..) that will be another gold skating-medal for Holland!

Part 2/2 (19-2-2014, after the race)

Yesterday I had more then 600 visitors of this blog-post ( 99% Dutch) which was a 'PR' for me ..and one of you responded on my invitation to track the race 'live' with my Excel 'in the cloud' (see download-mirror #1). But this unkown user X didn´t finish it, so I did it myself. But I think the reason X stopped was because the formulas were not being calculated well because he/she didn´t fill the cells in the correct format.Maybe I should have given a more detailed explanation how to fill the cells. So I´ll do that now.
As I said before, you should enter the (total/cumulative) laptime in the (red) columns, in format: nn:ss.mmm (minute, sec., milli-sec, e.g. 10:11.710). That means that every cell where you must enter the lap-times has 8 characters, which makes the calculation from the lap-times to milli-seconds more simple, as you can use for every cell a formula like this (e.g. for cell D14):

= (60000*MID(D14;1;2)) + (1000*MID(D14;4;2))+RIGHT(D14;3)

Another thing which I didn´t mention in part 1 is that the column 'Time Diff. with fastest' only will be calculated after the lap-times of all 3 skaters for one lap have been entered. But because the favourites for the top 3 positions were in the last races, and in the end the top 3 indeed came out of these last races, this was not a problem.

And now let´s look at the end-result (fig.2-3):

Fig.2 shows:
- chart 3: the winner (S1: Bergsma) and runner-up (S2: Kramer) were both faster then the old OR.
- chart 1 and yelow cells in table: skater (S2: Kramer) was the fastest until lap 22 (8800 m), from then on skater (S1: Bergsma) was the fastest.

Fig.3 shows:
- table 1, last column, green 'up-arrows' show that skater (S1: Bergsma) after lap 1 kept on going faster
- table 2, last column, red'down-arrows' show that skater (S2: Kramer) after lap 20 slowed down and in chart 2 you can see that his laptime was 700 ms slower then the previous lap while skater (S1: Bergsma) after the first laps never lost more then 200 ms.

So this race ended in another  victory for the Netherlands, winning all 3 medals. Congratulations to all of you, and especially to Bergsma, who said before the start of the olympics that in case in the Netherlands the winter would come and the canals would freeze and there would be the skating-marathon '11 cities tour', probably the most heroic competition in the world, in that case he probably would´t have participated in Sochi... Here a video of this tour:

I´ll hope one day Bergsma can win it too.




fig.1: Demo-data 10km skating

fig.2: Results: Top 3 race

fig.3: Results: Top 2 race


* Mirror #1:



* Mirror #2:


* Mirror #3:

1 comment:

Anonymous said...

I like it a lot