# Data analysis – basic knowledge of ms excel and physics

Using the GPS Data of the “Internet of Things Module”, please build a report or a dashboard calculating the KPIs indicated in slide 23 of the Presentation.

Feel free to use any solution (Excel included). Please share the report/dashboard source for evaluation.

Activity 3 CHALLENGE:

Using the GPS Data of the “Internet of Things Module”, please build a report or a dashboard calculating the KPIs indicated in slide 23 of the Presentation.

Feel free to use any solution (Excel included). Please share the report/dashboard source for evaluation.

Activity 3 INFO:

This activity needs some basic knowledge of MS Excel and Physics.

All you need to now is about the calculation of Velocity = distance/time and the conversion of the elements into different scales (i.e., we have velocity in km/h and we need instead m/s).

If you notice from the data of the GPS we have, it takes a data read every 0.1s, so this already helps your formula, right? the “time” value is in general 0.1 (you can use 0.1 as standard, or you can calculate the time difference between rows, in case the GPS misses a reading).

The speed as mentioned is in km/h to have m/s we need to divide the speed value by 3.6. Said that, we now need to get the distance value from the initial formula:

· Distance = Velocity(m/s) x time

· Distance = Velocity/3.6 x 0.1

This now tells us the distance ran by a player in that specific row. If we sum all the rows now, we know the total distance.
How about Acceleration for every row?

· Velocity = Acceleration * time

· Acceleration (m/s^2) = Velocity (m/s)/ time(s)

· Acceleration = (Velocity/3.6)/0.1

With those in mind, now you just need to know the basics of MS Excel.

Example: distance at more than 15km/h

• Create a new column and use the formula IF (Velocity CELL>15, Distance CELL, 0). You then sum all the values, and this will tell you about the total distance during the training at more than 15km/h…

I hope this helps. Please let me know via email. I will publish this also in general forum so you can respond there too.

There are videos and a sample excel for activity 3 (video and XLS sample)

Johan Cruyff Institute: Sport Data Analytics
Activity 3 Guidelines

Sport Data Analytics
ACTIVITY 3 GUIDE

Johan Cruyff Institute: Sport Data Analytics
Activity 3 Guidelines

Activity 3 help info:

This activity needs some basic knowledge of MS Excel and Physics.

All you need to now is about the calculation of Velocity = distance/time and the conversion

of the elements into different scales (i.e. we have velocity in km/h and we need instead m/s).

If you notice from the data of the GPS we have, it takes a data read every 0.1s, so this already

helps your formula right? the “time” value is in general 0.1 (you can use 0.1 as standard, or

you can calculate the time difference between rows, in case the GPS misses a reading).

The speed as mentioned is in km/h to have m/s we need to divide the speed value by 3.6.

Said that, we now need to get the distance value from the initial formula:

• Distance = Velocity(m/s) x time

• Distance = Velocity/3.6 x 0.1

This now tells us the distance ran by a player in that specific row.

If we sum all the rows now we know the total distance.

How about Acceleration for every row?

• Velocity = Acceleration * time

• Acceleration (m/s^2) = Velocity (m/s)/ time(s)

• Acceleration = (Velocity/3.6)/0.1

With those in mind, now you just need to know the basics of MS Excel.

Example: distance at more than 15km/h

• Create a new column and use the formula IF (Velocity CELL>15, Distance CELL, 0).
You then sum all the values and this will tell you the total distance during the training

at more than 15km/h…

I hope this helps. Please let me know via email. I will publish this also in general forum so

you can respond there too.

There are videos and also a sample excel for activity 3 (video and XLS sample)

https://www.dropbox.com/sh/ay19ft7s3bm90xd/AABBDNLl5tArzTjX6CS8hbMAa?dl=0 