When I reviewed the excellent Bontrager Ant+ speed and cadence sensors I included a couple of throwaway charts to illustrate the potential value of the second-accurate data they obtain.
The first was from a 200km brevet, looking at the time spent in each gear:
The second was from a Strava hill climb segment (can you tell when I had to change down each time?):
You don’t need the Bontrager sensors to do this, by the way – speed+cadence is ideal).
This article is in response to a request for more detail on how these graphs were devised and will allow you to do the same (providing you have a suitable sensor and GPS head unit).
Step 1: get your data in tabular format
Precisely how you do this will depend on what sort of application you use to store and review GPS data. I’ve been stuck on Ascent for a while now, and it’s easy to grab the data you’re interested in just by selecting the track and using the Export… as CSV option:
Step 2: Import to your favourite spreadsheet application
For CSV into Excel, you want to get a blank sheet open and use Data > From File (probably choosing comma as a delimiter) – google working with CSV files if you can’t get your head around it.
You should end up with something that looks like this:
The important thing is that you have the following columns: speed, cadence, distance.
I’ve removed the other columns to make things clearer, but you don’t have to do this:
Step 3: Convert speed into inches-per-minute
You can do this all within one cell, but I’ve separated it for clarity. The first new column I’m adding is the speed, converted into inches-per-minute (1mph is exactly 1056″ / minute).
Use the following formula (replace B2 as appropriate) and fill down the whole dataset:
Why inches per minute? Because we want to find out gear inches, and cadence is measured in rotations per minut!
Step 4: Calculate gear inches
Have a look at the following formula:
speed = Gear inches * pi * cadence
Gear inches = (speed / cadence) / pi
You can do all of this in one cell in Excel, but I’ve split it in two to make it absolutely clear:
Congrats, you’ve calculated gear inches!
Step 5: Scatter chart (distance VS gearing)
There are a bunch of things you can do now. One interesting option (say, for a hill climb) is to visualise the data with a scatter chart:
Here are the results:
In this case you can see I started just under 70″, dropped three times then settled at 39″ for the duration (bottom gear!)
Incidentally, the variation is caused by the low granularity of the cadence measurement. Say you’re cranking at 59.5rpm then at 60.49rpm (both recorded as 60rpm) on a 70″ gear. Your speed would be 0.2mph faster at the second data point than the first, but for the same recorded cadence.
The spreadsheet can only interpret that as a tiny change of gear (much smaller than a real gear change) but this fuzzes up the graph. Ignore it!
Step 6: Calculate time spent in gear
Another interesting thing to look at is how long you spent in each given gear. For optimum accuracy, obviously you need to use 1 second instead of smart recording!
To display this a little more spreadsheet effort is needed – but not much.
First, manually create a column containing the gear divisions you’re interested in (I only used increments of 5, you could go for increments of 1 if you want a smoother graph):
Now the tricky bit: we want a second ‘Time’ column next to this one, counting the number of rows in your gear inches column that are in the desired range. For this you need to use a couple of
countif functions as follows:
In other words: count the number of gearing rows greater than this size, minus the number of gearing rows greater than the next size. (There are probably a million ways to do this, but this works).
Because we’re using second recording, the count equals the number of seconds spent in each range. It would be more interesting for something longer than a ~70 second hill climb:
You can just do a simple line chart now, to see time spent in each gear:
It looks a bit more useful for a longer ride – you can see whether you’d get away with a different sized cassette, for instance, or if you’re speccing a TT bike for your local course, which chainring size would allow you to stick most closely to the centre of your block.
In my case, it shows the irrelevancy of gearing over 100″ (amongst other things):
Anyway, hope that was vaguely interesting!
Any other ideas? Anything you can improve on or change? Please drop me a comment using the form below…