Quite recently I was teaching a class, afterwards I got chatting to one of the participants and it turned out that she does a lot of coaching with runners. Of course I picked her brains about some stretching techniques and she gave me some great tips about pre-running stretching and then she asked me about using Excel to track the performance of her athletes. So this is what I came up with…
Part 1 – the setup
So I set up a spreadsheet for her which had the following headings:
- Name
- Date
- Hill Time
- Hill & Recovery Time
I began by entering the headings and some dummy data and converting the whole list to a table (Ctrl and T or Insert – Table)
The Hill Time and Hill & Recovery Time had to be entered as time (minutes and seconds). That was done by using the Format Cells option. I did that by highlighting the column and then pressing Ctrl and 1 to get the Format Cells option.
- Highlight the column for Hill Time
- Press Ctrl and 1 to get the Format Cells option. (I show you another way to get this in the video below)
- Click on Custom on left
- Locate the following format ([h]:mm:ss) – without the brackets…if it’s not there, click on one of the Time options (some variation of hh:mm:ss) and enter the [h]:mm:ss format
- You need this format so that Excel will read your entries as minutes and seconds.
- Repeat for Hill & Recovery Time column and Recovery Time column
Note that when you are entering the actual times in these two columns you will have to enter it as follows: 0:12:34 (using a colon to separate the hours, minutes and seconds)
Once you have that done, next step is just to enter a formula in the Recovery Time column to subtract the two times from each other. Because the list has been formatted as a table, as you enter the two times, the answer will appear automatically.
[fvplayer src=”This is a public linkhttps://s3-eu-west-1.amazonaws.com/the-excel-expert/running_time_calculations.mp4″]
Part 2 – Further analysis
It then occurred to me that it would be interesting to see if there was any patterns to this data (even though it was just dummy data) so I applied a pivot table to it and then a line chart from that.Of course this threw up a few interesting anomalies and dilemmas and you can view them in the video below..
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/running_time_calculations_part_two.mp4″]
The first steps for creating a pivot table are here.
Completed File