Analyzing your results

I took 3rd in my age group yesterday at the Cranberry Sprint, and I'll post a full race report tomorrow. Today, I wanted to talk about analyzing race results.

You see, this morning, I awoke to find the full results from the race posted to Cool Runnings. I wanted to find out how my discipline times stacked up against the rest of the girls in my age group, but with the raw dump of results it's hard to figure out. You can use excel, but excel is, let's just say, not very graceful with this kind of data. I had to use a handful of tricks to massage the data to a usable format. Here's how I did it.

Just the basics

First, copy and paste. I usually paste results in as text (Using excel, 2007, from the Paste drop-down, select Paste Special, then choose Text.) Depending on how the data is organized it may pasted into individual cells. If not, from the the Data menu, use Text to Columns to quickly spread the data into columns.

I then use an autofilter to view just my age group results (from the home menu, select Sort and Filter, then Filter to add the filter). In the column labeled div (i.e., division), use the drop down to select just your age group.


Then I can sort by each discipline to see how my times stacked up. Sorting by swim, I see that I had the 5th fastest swim in my age group. By bike, I had the 4th fastest time, and by run I had the 4th fastest time, which led to my overall 3rd place age group finish. Nice.

With a race where each of the discipline times is under an hour, this works well. But try sorting by Tottime (Total Time) smallest to largest. You'll see the folks who completed the race in under an hour end up at the bottom of the sheet, not at the top.


Fixing times
Excel is looking at the times for the top 9 finishers and thinking they did a 50+ hour race, not 50+ minute. To fix this you need to use several time functions: TIME, HOUR, DAY and MINUTE. Here's the equation you can use (where E2 is the Tottime):

=TIME(0,HOUR(E2)+24*DAY(E2),MINUTE(E2))

TIME takes 3 inputs: hours, minutes and seconds. Basically, this says, set hours to 0, set minutes to the hours field (plus days because the HOUR function doesn't return values great than 24), and set seconds to minutes. You can use this function for everyone in the top 9 in a separate column, then copy and paste-value to the Tottime field.

I use similar functions to this one to convert each of the discipline times, so they are comparable.

Fixing the rankings
Excel tries to be smart. When you enter 1/18 in the Div/Tot field (i.e., your rank within your age group), it reads that as the date Jan 18, 2011. This is probably helpful much of the time, but doesn't work when you're trying to show that a person was the first out of 18 in his age group. You'll notice for people in the top 12 of their age group excel is replacing the rank with dates, but after 12 it displays them properly. The short answer for how to fix this is:

=IF(ISNUMBER(F2),IF(YEAR(F2)=2011,CONCATENATE(MONTH(F2),"/",DAY(F2)), CONCATENATE(MONTH(F2),"/",YEAR(F2)-1900)),F2)

Basically, this function first checks if the entry has been converted to a date, then it recreates the ranking as a text item. (There's also a little funny business in there about the year being outside of 2011.)

Cumulative time

Finally, I like to look at my positioning in the pack at relevant points in the race. Having fixed each of the discipline times above, I create a cumulative time exiting T1, entering T2, exiting T2, to see how I'm fairing versus the competition. It's pretty simple: Exiting T1 = swim time + T1 time, Entering T2 = Exiting T1 + bike time, Exiting T2 = Entering T2 + T2. I then graphed the resulting ranks in powerpoint (I'm in orange!).


You can see the first woman out of the water held onto her lead throughout the race. I came out of the water 5th, passed one person in transition and another on the bike to put myself in 3rd place, which I held onto for the remainder of the race.