15 Dec 2015

Statistics result 10-km run Carrera de las Empresas Madrid 2014

#48 Statistics result 10-km run Carrera de las Empresas Madrid 2014

Note 21-12-2016:
For stats (made with R and Power BI) of last edition of this race, see this post:
http://worktimesheet2014.blogspot.com.es/2016/12/carrera-de-la-empresas-2016-madrid.html


Yesterday, so Sun. 13 Dec. 2015, the 10K run "Carrera de las Empresas" was held here in Madrid.
In this 'business run', companies (teams of collegues) compete in 18 categories (dimensions: number of runners in team (2,3,4), gender team-members (men, women, mix) and distance (6, 10 km)). I also wanted to participate with my company, Ilunion (coparate group ONCE), like I did last year, but unfortunately this time I could´t because I was injured. For the 2014 edition of this run I made an Excel with the results but I never published it because of time problems, but I thought to finish it last sunday, so although I did´t participate, my mind was with the race..

In the 2014-edition there were in total about 9000 runners (about 300 teams) from about 800 companies, For more info about the run, see:

http://www.carreradelasempresas.com/

http://www.expansion.com/2014/12/14/entorno/1418574269.html

and for some photos:

http://www.expansion.com/albumes/2014/12/15/xvi_carrera_de_las_empresas/index.html

and for some statistics:

http://www.runedia.com/cursa/201419683/carrera-de-las-empresas-10k-actualidad-economica/2014/

In fig.1 you can see the result, and, as always, in the end of this post you can find the
 download-URL´s of the Excel(s).


fig.1 statistics result (finish times) 10K run

So my Excel only has statistics of the 10K run, which (netto/chip-) finish-times I got from this PDF:

http://estaticos.expansion.com/opinion/documentosWeb/2014/12/23/ABSOLUTA%2010K.pdf

As you can see in this PDF, for each runner his team is normally specified as:
Name company +´'-' + number or letter, e.g. my team was named Ílunion-26.
I wanted to create also statistics about the companies (number of runners per company), so first I transformed the data of this PDF to get the company of a runner, so for my row in this Excel the transformation was: team  Ílunion-26 -> company  Ílunion. Then I calculated in the Power Pivot datamodel, with a DAX-formula 'distinct count' the number of companies (687) derived from the field team (total: 1848), see fig.2.



fig.2: calculating data 'Company' (EquipoDef) from field 'Team' (Equipo).


And then I created a statistic 'total runners per company', see fig.3 for top 25 companies
 (with most runners). NB: I don´t know if 'TR' is a company-name or maybe a dummy-value.


fig.3: total runners per company


I wondered if there was a correlation between the number of runners of a company and the best 'total-times' of a team (of 2,3 or 4 members).  I used the category '10K - 2 (male) runners per team' to test this, see fig.4 for the result, which shows the correlation is weak (-0.15, so far for the max (negative) correlation of -1). And in the graph (scatter-plot) you can see that companies with about 30 runners or more always have a total-time (sum of time of runner 1 (best runner of company) and runner-2 (2nd best runner of company) lower then 5000 sec, which is not the case for smaller companies (in the 'bin' of companies with 2 to 10 runners, the slowest total-time is about 8000 sec.). Although in this category, the winner was a team of a company with only 2 runners, from New Balance. I guess they run on NB-shoes, so this run can be a nice way to get some free promotion...



fig.4: correlation between the number of runners of a company and the best 'total-times' of a team


Downloads:

#Mirror 1: Google Drive (zip files with 2 Excel and PDF files):



#Mirror 2: Microsoft Onedrive (1 Excel file):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC

http://1drv.ms/1O4FnIu


#Mirror 3: Scribd.com (1 PDF file):

https://es.scribd.com/doc/293387909/Statistics-10km-Run-CarreraEmpresas2014-v2-R1







7 Dec 2015

Month-calendar for December 2015 in Excel

#47 Month-calendar for December 2015 in Excel

In this post the new month-calendar for December 2015, see fig.1. The Excel also has the year-calendar for 2015 and a 2015-year-planner. Event of this month is Carrera de las Empresas 2015 Madrid (13/12), a 10K run, a competition between (teams) of companies, so an event where work and sports meet like in this blog. For more info about this event , see:

http://www.carreradelasempresas.com/

http://www.expansion.com/actualidadeconomica/2015/11/02/5633741b46163f905f8b45b5.html



                                          fig.1 Dec.2015 calendar


Downloads:

https://drive.google.com/file/d/0BywxxSJoaUYxQVdFeFpESDlhRUU/view?usp=sharing