0

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…

hill_seeker

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.

  1. Highlight the column for Hill Time
  2. Press Ctrl and 1 to get the Format Cells option. (I show you another way to get this in the video below)
  3. Click on Custom on left
  4. 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
  5. You need this format so that Excel will read your entries as minutes and seconds.
  6. Repeat for Hill & Recovery Time column and Recovery Time column

running_up_hillNote 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.

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..

The first steps for creating a pivot table are here. 

 

Completed File

View completed file here

 

 

Leave a Reply