Graph gear inches vs distance or time

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:

gear-inches-graph2

The second was from a Strava hill climb segment (can you tell when I had to change down each time?):

gear-inches-graph

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:

gpx-export-csv

 

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:

gpx-in-excel

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:

gpx-in-excel-cut-down

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:

=B2*1056

excel-calculate-gear-inches

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:

excel-pi-gearing

excel-gearing

excel-gearing-done

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:

excel-chart

Here are the results:

excel-done

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):

excel-gear-range

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:

=COUNTIF(G:G,">"&I2)-COUNTIF(G:G,">"&I3)

excel-geartime-calc

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:

excel-geartime-filled

You can just do a simple line chart now, to see time spent in each gear:

excel-geartime-chart

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):

gear-inches-graph2

Anyway, hope that was vaguely interesting!

Feedback

Any other ideas? Anything you can improve on or change? Please drop me a comment using the form below…

8 Comments

  1. Matěj Novotný

    Nice tutorial. But what does it mean “distance VS gearing”? What does it say to me?

  2. Dave

    It’s a plot of the distance travelled against the gear size used.

    For instance if you rode up a hill then down the other side, and each slope was a mile long, you would see the first half of the graph showing the gear dropping as you got tired, then it would increase as you descended (and selected a higher gear).

    You could also look at effects such as fatigue on cadence / gear choice over a time trial course (or anything, really).

  3. Matěj Novotný

    It’s simpler than it seamed to me on first look. Thank you.

  4. Simon

    Excellent article. Many thanks

  5. Hi Dave

    I was inspired by this write-up, so decided to investigate my own cassette / sprocket usage.

    What I did was to use the Garmin GSC-10 pedal and wheel data to calculate which gear I was in for each data point recorded. My write-up is here: http://www.hutsby.net/2013/12/what-gears-do-i-mostly-use-on-my-bike.html.

    So using this I can see that on the ride whose data I used, I just don’t use most of my cassette… NB because I’ve set my Garmin to record every second, I don’t need to do anything to get the frequency distribution.

    One thing I was curious about was if the sampling method and frequency was sufficient to get accurate enough data. The graphs in the above write-up suggest that they are, because you can see the results clustered around what the cassette offers.

    This wasn’t enough though! So I took a longer ride, and again plotted some data, and then created a table and chart with theoretical values that assume everything was statistically valid. The results of this are here: http://www.hutsby.net/2013/12/more-thoughts-on-bike-sprocket-cassette.html. As we see we get a very nice alignment of theory and actual.

    Anyway, thanks for inspiring me to try all this out!

    Steve

  6. Dave

    Great work Steve!

    Both of those are really solid articles. I’m especially pleased that you got it to line up so well in the second – nice validation.

    Looking at your results, you can see that quite a lot of time is spent in the 11/12t (especially on the longer ride).

    As your export should also have elevation, you could extend your analysis to remove data points where the gradient is significantly downhill (we all run out of gears downhill) and then if you still had a lot of time in the smallest cogs, that would suggest a larger chainring would improve efficiency, since the smallest teeth are significantly draggy. (Or conversely, if that was all downhill, you’d know your big ring is the right size for you).

    Thanks for sharing!

  7. Hi Dave

    Thanks for the comments!

    I’m not sure that sample to sample that the elevation data recorded is accurate enough. We all know that GPS is near useless for accurate height, which is no surprise since it wasn’t intended to accurately measure altitude. Also even with a super clear view of the sky and a decent constellation of satellites available I hardly ever get better than 5m accuracy, as displayed by my Edge.

    The Edge 500 has a barometer in it which will improve things. Garmin say the Edge devices are accurate to about 10 feet, call it 3m .

    What could be done, is to take data points from climbs that have a decent duration ie we know that for a period of X minutes we were climbing. So I had a go at this.

    This is a Strava segment I did last week:

    http://app.strava.com/activities/100999188#2166692774

    Its a ~4km ~180m climb which Strava says has an average gradient of 4.6%, and created this chart:

    I’m not really sure what this means… I didn’t come off the big ring (I almost never come off the big ring which is why these calcs can be done!), and my HR averaged about 144 for the climb which is about 20 to 25 below my max at that temperature. I’ve done the climb faster previously (same cassette), but unfortunately I didn’t have wheel cadence on the previous rides on that segment so can’t compare to that degree.

    PS Let me know if the chart doesn’t show up OK, and I’ll think of a different way to present it.

    Cheers

    Steve

  8. Robert

    Steve:
    Unfortunately, your graph did not show up. Perhaps you can post it on your blog?
    I’ve done similar things here: http://anonymous.coward.free.fr/wattage/components/components.html
    In particular, it’s possible to examine either cadence vs. gradient as here: http://anonymous.coward.free.fr/wattage/components/sorenson-tdf10-s17-2.png
    or
    http://anonymous.coward.free.fr/wattage/cadence/larsson-stage3.png
    with a zoomed-in view here:
    http://anonymous.coward.free.fr/wattage/cadence/larsson-toc09-3.png

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>