Excel 2013 and Business Intelligence

#14  Excel 2013 and Business Intelligence

NB: This post has 3 Excel-files (embedded in 1 zip), see ‘Downloads’ at bottom of page. To be able to open these files, you must have Excel 2013 with the Power-BI add-ins installed.

Recently I followed a very interesting course about Business intelligence (BI) with MS SQL Server 2012, Analysis Services 2012 and MS Excel 2013. Because this blog is about Excel,  I´ll tell in this post something about Excel 2013 and it's BI-features.

The teacher of this course, who has a blog about BI, see:

www.amby.net

said: “Microsoft wouldn’t be Microsoft without Excel”, meaning that Excel is probably Microsoft's most used product. And with Excel 2013, and it´s 'Power-BI' add-ins like PowerPivot, PowerQuery and PowerView, Microsoft  wants to provide 'self-service business intelligence' (so for business-users), to increase even more the use of Excel.

To start, a definition of BI, from:

http://en.wikipedia.org/wiki/Business_intelligence

Business intelligence (BI) is a set of theories, methodologies, architectures, and technologies that transform raw data into meaningful and useful information for business purposes.

An example of BI with Excel is e.g. a spreadsheet which has all the sales-data of a big international company and then by applying pivot-tables and pitvot-charts, this data can be summarized and presented in a way the general salesmanager can see easily how the business is going, e.g.  the top 10 of most sold products, or the sales per country.


* PART 1

Excel: Skating_XL_PP_Query10_XL13C_v2.xlsx

In this part I´ll use the same database as in the previous post, see:

http://worktimesheet2014.blogspot.com/2014/04/excel-and-relational-databases.html

So first I link my Excel to the Excel-file with the Skating-database (C:\Temp\Skating_DB_XL.xlsx, see 'Downloads' in my previous post), by creating the connection, see fig.1.

fig.1: Connection (relational) database

Then I add the linked-tables in PowerPivot, into a 'tabular model', which is the data-source I'll use from now on to create pivot-tables etc, see fig.2-4.

fig.2: Connection PowerPivot

fig.3 PowerPivot datamodel

fig.4 PowerPivot datamodel: relations

With the Skating-database (3 tables: Race, Skater, Team) imported in the datamodel in PowerPivot, I now can use DAX-formulas (Data Analysis Expressions), which are like ‘normal’ Excel-formulas, except that they work on (rows and columns of) tables (not on cells). For more details about DAX, see e.g.:

http://office.microsoft.com/en-us/excel-help/data-analysis-expressions-dax-in-power-pivot-HA102836919.aspx

2 Examples of DAX-formulas:

*1: Column: Category_MW (Man/Women) = LEFT(T_Race[Race];1), see fig.5. Result: M/W.

*2: Total distinct races  = DISTINCT_Distance:=DISTINCTCOUNT([Distance]), see fig.6. Result: 6.
NB: for this total, we only look at distance, so disregarding gender, so M500 = W500 (race:  men/women over distance 500 meter)

fig.5 DAX-formula

fig.6 DAX-formula

Leaving the PowerPivot-window, you can see in Excel 3 pivot-tables:

*1: Number of skaters per distance (race), with slicer (filter): Gender (Men/Women), see fig.7

*2: Number of skaters per distance (race) X Gender (Men/Women), see fig.8

*3: Medal-total (ranking 1,2,3: gold/silver/bronze) per skater and Gender (Men/Women), see fig.9.

fig.7: Pivot-table 1

fig.8: Pivot-table 2

fig.9: Pivot-table 3

Besides the pivot-tables, the Excel also has a report in PowerView, see sheet-1 and fig.10 for an example. This report has several elements:

* on the top-right, all tables and fields (including the calculated-fields like ‘distinct Distance’) of the database (from the PowerPivot-datamodel, see above)

* the tables in the report (‘Ranking per Skatername’ and ‘Team/Skater-name’ and ‘distinct Distance’)

* filters  (e.g. ‘Gender’ and bar with 1 value per race ‘M1000, M10.000 etc’) which work on the tables, so they make the report ‘interactive’

In fig.10 you can see that:
- in race M1000 (Men 1000 meter) (see slicer), there were 4 (Dutch) skaters participating, and Groothuis won this race (table 1).
- because filter Gender = ’M' (Men), table 2 (Team/Skater-name) shows only the men (not women) in the teams, and the number of races (distinct distance) for men is 5.

fig.10 Report in PowerView

* PART 2

Excel: Skating_PowerviewMap_v3.xlsx

In this Excel you can see how in Excel 2013 with PowerView you can visualize geospatial data in a map.
I created a table in in Excel with the top 10 countries of the medal-ranking from Wintergames 2014, which I copied from:

http://en.wikipedia.org/wiki/2014_Winter_Olympics_medal_table

and I added an extra column, 'Continent', and this table I imported in a 'tabular model' in PowerPivot, see fig.11.

fig.11: datamodel in PowerPivot

After that I enhanced this model by specifying the correct Data Category (in menu Advanced) for the 'Continent' and 'NOC' (Country) fields.  And then when you return to Excel and want to create a report for this data in PowerView, you see that these geographic-columns are marked with a Globe-icon. The maps used by PowerView are those from MS Bing Maps, so you should be 'online' when you want to create a report with maps. The final result you can see in fig.12-13, which show in this case the number of golden medals won per continent and country, with a dot. So you can see easily (by the size of the dot) that in Europe the countries in the North did better then in the South or that a small country like the Netherlands did better then a big country like France.
For more info about the use of Excel and  Maps, see e.g.:

http://www.sqljason.com/2012/07/creating-maps-in-excel-2013-using-power.html

fig.12: PowerView with Bing-Map

fig.13: PowerView with Bing-Map

* PART 3

Excel: WebDB_v2.xlsx

With Excel 2013 and add-in PowerQuery, you have a lot of possibilities to connect to external data and the Excel in this part shows how you can import a table from the Web, in this case I used the 'Overall top goalscorers' table  from:

http://en.wikipedia.org/wiki/UEFA_European_Football_Championship

see fig.14

fig.14 Website with topscorers-table to import in Excel

and for the result, see fig.15-16.

fig.15 topscorers-table import by PowerQuery

fig.16 topscorers-table import by PowerQuery

This post just showed some aspects of Excel 2013 and BI, for more, see e.g. :

http://blogs.msdn.com/b/microsoft_business_intelligence1/archive/2012/07/23/microsoft-business-intelligence-in-excel-2013-sharepoint-2013-and-sql-server-2012-sp1.aspx

Note 11-5-2014: Today I created a new post about Excel 2013 and PowerMap (another add-in of the Power-BI suite), with an example for 2014 FIFA World Cup Soccer, see:

http://worktimesheet2014.blogspot.com.es/2014/05/excel-2013-powermap-and-world-cup-soccer.html

Downloads

https://drive.google.com/file/d/0BywxxSJoaUYxVmJRV2VRcmF1a0k/edit?usp=sharing

Excel and relational databases

#13  Excel and relational databases

NB: This post has some files (embedded in 1 zip), see ‘Downloads’ at bottom of page.

In my previous post:

http://worktimesheet2014.blogspot.com/2014/02/rankings-dutch-skaters-in-wintergames.html

I showed how you can calculate the medal-totals grouped by team, and I also said that this calculation could be much simpler in SQL, the language used in relational databases, like e.g. MS Access. In this post I´ll show you how you can do this in MS Excel, so in a spreadsheet-program. This is useful for those who don´t have an MS Office-version which includes MS Access.

The general idea is to split the application in 2 layers (2 Excel-files): the database-layer and the client-layer, like in a client/server-application.

The database has data about skaters, races and teams. Each type of data is put in it´s own table and between the tables exist relations as you can see in fig.1, e.g.: a team consists of 1 or more skaters and a skater participates in 1 or more races. The tables are linked by their primary keys (PK) and foreign keys (FK), columns which end with ID, e.g. TeamID (PK of table Team and FK of table Skater).

fig.1: Tables in database

The data in these tables you can see in fig.2 and file Skating_DB_XL.xlsx, which is an Excel-workbook with 3 worksheets, one for each table.

NB: I also made this database in MS Access for illustration-purposes, see fig.3 and file Skating_DB_AC.accdb 

fig.2: Excel-file with database

fig.3 Access-file with database

To be able to use these 3 tables in this Excel-file (´database-layer’) in another Excel-file (the ‘client-layer’), you must create a ‘Range’ for each table, see fig.4 and these sites how to do it:

http://office.microsoft.com/en-gb/excel-help/define-named-cell-references-or-ranges-HP005201536.aspx

http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.html

fig.4: Named Ranges for tables in database

In the Excel with the client-layer, you now must create a connection to the Excel with the database-layer, which is stored in a dqy-file (Excel ODBC Query), see these sites how to do this:

http://office.microsoft.com/en-us/excel-help/use-microsoft-query-to-retrieve-external-data-HA010099664.aspx

http://exceluser.com/formulas/msquery-excel-relational-data.htm

With the connection to the database created, you now can use Excel Query to create your queries to the database,  using the Query Wizard (see fig. 5-6) or hand-written SQL (see fig.7 etc.)

fig.5: Excel Query

fig.6: Excel Query with result

Some examples of database queries in SQL:

* Example 1

File: Skating_XL_Query2b.xlsx, Skating_XL_Query2.dqy

Query: ranking for all skaters in top 3 and pivot-table

SQL:

SELECT 

 T_Race.SkaterID,
T_Skater.Gender, T_Skater.SkaterName,
T_Team.TeamID, T_Team.Trainer, T_Team.TeamName,
 T_Race.Race, T_Race.Ranking

FROM `C:\Temp\Skating_DB_XL.xlsx`.T_Race T_Race,
`C:\Temp\Skating_DB_XL.xlsx`.T_Skater T_Skater,
`C:\Temp\Skating_DB_XL.xlsx`.T_Team T_Team
WHERE T_Race.SkaterID = T_Skater.SkaterID
AND T_Skater.TeamID = T_Team.TeamID
AND T_Race.Ranking <=3

fig.7: Result query

fig.8: pivot table based on result query (fig.7)

*Example 2

Files: Skating_XL_QueryXL_6b.xlsx, Skating_XL_Query6.dqy

Query: medal-total per team

SQL:

SELECT

 Sum(IIF(T_Race.Ranking=1,1,0)) AS 'Total_Gold',
Sum(IIF(T_Race.Ranking=2,1,0)) AS 'Total_Siver',
Sum(IIF(T_Race.Ranking=3,1,0)) AS 'Total_Bronze', Sum(IIF(T_Race.Ranking=1,1,0))+Sum(IIF(T_Race.Ranking=2,1,0))+Sum(IIF(T_Race.Ranking=3,1,0)) AS 'Total',
 T_Team.TeamName
FROM `C:\Temp\Skating_DB_XL.xlsx`.T_Race T_Race,
`C:\Temp\Skating_DB_XL.xlsx`.T_Skater T_Skater,
`C:\Temp\Skating_DB_XL.xlsx`.T_Team T_Team
WHERE T_Race.SkaterID = T_Skater.SkaterID
AND T_Skater.TeamID = T_Team.TeamID
AND ((T_Race.Ranking<=3))
GROUP BY T_Team.TeamName
ORDER BY Sum(IIF(T_Race.Ranking=1,1,0)) DESC, Sum(IIF(T_Race.Ranking=2,1,0)) DESC,  Sum(IIF(T_Race.Ranking=3,1,0)) DESC, T_Team.TeamName

fig.9: Result query

Note 7-5-2014:
I wrote a new post with more examples on the Skating-database in this post, using PowerPivot and PowerView, see:

http://worktimesheet2014.blogspot.com.es/2014/05/excel-2013-and-business-intelligence.html

Download-mirror:

https://drive.google.com/file/d/0BywxxSJoaUYxaTVxb0FxcmdOWVE/edit?usp=sharing

Rankings Dutch skaters in Wintergames Sochi 2014

#12  Rankings Dutch skaters in Wintergames Sochi 2014

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.1: Ranking Dutch skaters in races at Olympic Wintergames 2014 Sochi

Fig.2: Dutch Skaters with King Willem Alexander and Queen Maxima,

source:

http://www.vorsten.nl/wp-content/blogs.dir/6/files/2014/02/ANP250214273-1

Part 2/3 (27-2-2014)

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)

So this formula loops through all rows with the skaters and if the skater is of team TVM, it includes this skaters golden medal-total into the TVM-team´s golden medal-total. For the result, see fig.3, which shows that team TVM, with trainer Kemkers, had the best performance, with a total of 7 medals.

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

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:
http://worktimesheet2014.blogspot.com/2014/02/results-speed-skating-5000-men-at.html
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:

http://www.wedden-op-favorieten.nl/olympische-spelen/kanshebbers-10km-schaatsen-10000m-18-02-2014/

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:

http://www.youtube.com/watch?v=QlEDQgPi1bw&feature=youtube_gdata_player

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


References

http://wiekvoet.blogspot.com.es/2012/12/speed-skating-10-km.html


Figures

fig.1: Demo-data 10km skating

fig.2: Results: Top 3 race

fig.3: Results: Top 2 race

Download-mirrors:

* Mirror #1:
Race:
https://skydrive.live.com/redir?resid=3F963E9F1A42D952%21207

Demo:
https://skydrive.live.com/redir?resid=3F963E9F1A42D952%21204

Empty:
https://skydrive.live.com/redir?resid=3F963E9F1A42D952!203&authkey=!ADgfLAJzA8BSsDM&ithint=file%2c.xlsx

* Mirror #2:
Race:
https://docs.google.com/file/d/0BywxxSJoaUYxTzhZdVhzeEw1NGs/edit

Demo:
https://drive.google.com/file/d/0BywxxSJoaUYxdXdNSVVJaEZhMHc/edit?usp=sharing

* Mirror #3:
Race:
http://www.scribd.com/doc/207931512/Results-10km-Men-speed-skating-Sochi-2014-in-PDF

Calendar 2014 v8 - with Moon-phases and Macro (VBA)

#10 Calendar 2014 v8 -with dates seasons and moon-phases and Macros (VBA)

I made a new version (v8) of my Excel Calendar, which is almost the same as v7, except it now includes the dates of the seasons (equinox, solstice) and moon-phases (new moon, full moon). These 'time events'  are reflected in some pictures in sheet-1 (Info), which are enabled or disabled depending on the actual date, or where you live, see fig.1, 2 for how the calendar looks like for some who lives north/south of the equator. To do this, I used macros and VBA (Visual Basic for Aplications, the basic language of Microsoft Office). That´s why the extension of the Excel now is xlsm (in stead of xlsx), which may cause a warning when you download it, but you can ignore that (so choose 'enable macros').
For more details on VBA, see e.g. :

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

fig.1: 11-2-2014: Summer in South

fig.2: 11-2-2014: Winter in North

Download-mirrors:

* Mirror #1:
https://skydrive.live.com/redir?resid=3F963E9F1A42D952!200&authkey=!AHwzHvWwLs60JBw&ithint=file%2c.xlsm

* Mirror #2:
https://drive.google.com/file/d/0BywxxSJoaUYxcUUzMmoyRXNrYTA/edit?usp=sharing

Results Speed Skating 5000 Men

#9  Results Speed Skating 5000 Men at Olympic Wintergames Sochi 2014

After the great success of the Dutchmen in Speed Skating 5000 Men at Olympic Wintergames Sochi 2014 (gold, silver and bronze), I decided to create an Excel with the result of the top 6. So I took the times of the 6 best skaters, and did´nt look at the times of their oppenents. And I took the top 6, because the skater who finally ended at rank 6 for some laps had the best (intermediate) time. The Excl shows how to make a X Y Scatter Chart (so for distance/time).
To make the calculations a bit more easier, I converted all lap-times to milli-seconds.
I made a comparison of the laptimes of the 6 best skaters and looked in more detail at the times of the winner, Sven Kramer, who improved the Olympic Record. Well done and congratulions Sven!

Note 10-2-214:
All the end-standings of this race, you can find at:

http://www.sochi2014.com/en/speed-skating-men-s-5000-m

Today I found this website, where you can find all skate statistics, e.g. world-records, laptimes etc.:

http://schaatsstatistieken.nl/

And another nice (Dutch) site with actual information about skating, like the results at the Wintergames #Sochi2014:

http://schaatsen.blog.nl/

Note 12-2-214:

Last night  I was searching for skate-statistics, and found this (from a Dutch newspaper): see fig.6
So in this diagram they take the lap-times 'to beat' (world-record at that time), from 1 skater (Koss) as a reference and show the differences from 2 other skaters (Romme and De Jong) with respect to these times, and you can see both beat the record, with Romme as the winner.

I did the same in my Excel: so I changed the last column of the table in fig.2 for
the lap-times of the winner (Kramer), see fig.7 for the result.

For the Dutch readers: a little manual how to make skate-statistics in Excel (in Dutch):
http://sanctamariatest.mwp.nl/LinkClick.aspx?fileticket=fx%2BjURXW66E%3D&tabid=573&mid=2407

Note 13-2-214:
I made another chart to compare Sven Kramers new Olympic Record (OR) in Sochi with the old OR (of 2010, also of Kramer), see fig.8, which is in the same style of fig.7, with the OR of 2010 as a reference (x-axe).
Today I also looked at the statistics of a skating-tour 'Ijzeren Man' ('Iron Man') of 50 km which I made last year, in Vught, North Brabant, Netherlands, see:

http://www.gobreadcrumbs.com/user/mvanreek/other/netherlands/ijzeren-man-vught-nbrabant-netherlands-ice-skating-tour

and when I saw my average speed (15km/h) and max. speed, I thought it would be nice to add this also to the Excel, see fig.9  (the orange cells). As you can see, Kramer goes almost 50 km/h (so more then 3 times as fast as I went, although my race was outside and with old-style skates..).
And when I looked at the website of another skater:

http://www.mylaps.com/en/practice-results/activity/1718064?view=table#view-tabs

http://www.mylaps.com/en/practice-results/activity/1718064?view=chart#view-tabs

it gave me the idea to add also a table and chart with the difference in speed in one lap and the previous lap , and use 'conditional formatting' to add the 'up' and 'down' arrows, so the indicate if the speed increased or decreased, see also fig.9.

Figures:

fig.1: Times top 6

fig.2: Times top 6

fig.3: Times top 6

fig.4: Times top 6

fig.5: Times winner

fig.6: Grafiek_VK_Dec1997

fig.7 : Kramer vs rest top 6

fig.8

fig.9

Download-mirrors:

* Mirror #1:

https://skydrive.live.com/redir?resid=3F963E9F1A42D952!202&authkey=!AF2NzYW9rb-UNaA&ithint=file%2c.xlsx

* Mirror #2:
https://drive.google.com/file/d/0BywxxSJoaUYxSDc0YUpaYm1NT00/edit?usp=sharing

https://drive.google.com/file/d/0BywxxSJoaUYxTUZMRFMzalF4TDg/edit?usp=sharing

* Mirror #3:
Excel:
http://es.scribd.com/doc/206191376/REsults-Speed-Skating-5000-meter-Men-in-Sochi-Olympic-Winter-Games-2014

PDF:
http://es.scribd.com/doc/206192579/IceSkating5km-v1c-x-p2

Calendar 2014 v7 - UserForm with ComboBox

#8  Calendar 2014 v7 - UserForm with ComboBox

I made a new version (v7) of my Excel Calendar, which is almost the same as v6, except the data-entry UserForm now has a ComboBox control to select the language, see fig.1.
NB: control-objects like the ComboBox control are 'unsupported features' in Excel Web App (see download-mirror #1).

For more details on controls and Excel UserForms, see e.g. :

http://office.microsoft.com/en-us/excel-help/add-a-list-box-or-combo-box-to-a-worksheet-HP010342141.aspx

fig.1

Download-mirrors: 

Mirror #1:

https://skydrive.live.com/redir?resid=3F963E9F1A42D952!185&authkey=!ANZbhGF8pp0cDfU&ithint=file%2c.xlsx

Mirror #2:
https://drive.google.com/file/d/0BywxxSJoaUYxT25RdnBxM1RNRjA/edit?usp=sharing