Skip to main content

Cycling mileage spreadsheet (using google docs)

Several years back I was looking for a good way to keep track of my annual cycling mileage and a little Googling resulted in finding this website and it's link to a downloadabe Excel spreadsheet for keeping track of cycling mileage. Mark Pankin, who created that Excel doc annually updates the document and makes it available to the public. I think the document is great and I've used it for several years. One issue I did have with it though was gaining access to the document remotely. I kept it on my computer at home but sometimes I wanted to update it when I was not at home or just pull data from it, again when I wasn't at home.

I had had some email exchanges with Mark to ask about certain features of his document and this led to a discussion about porting the document over to Google Docs. Mark was not a Google Docs user but he didn't mind at all if I created a document using his Excel spreadsheet as a model. While there is some ability to import and export Excel format documents on Google Docs, Mark's spreadsheet was too complex to import intact, so I started from scratch, but as mentioned used Mark's Excel version as a model. I've added a number of features to the Google Docs version on top of what Mark had in place, and for a number of other functions, I had to recreate them using the Google Docs syntax. While Google Docs spreadsheets are not as robust as Excel, I think a very functional document emerged from the work.

In the Google Docs spreadsheet I created, there is a tab for 2014 mileage, and also I've gone ahead and created the 2015 tab as well with links between the tabs so that the previous year and previous best columns are auto populated beginning with 2015. It is fairly easy to create a new tab for each year. There are a handful of functions to either move from one cell to another, or to update. The demarcation between months was done by using thick borders in the Excel version, but on the Google Docs version I've used shading. Either works, but for each new tab I'll need to edit the shading for each month first (as Mark did with the borders).

The enhancements I've made include the table and chart of average weekly mileage at the bottom of the document, as well as some automatic shading in column A, the current monthly totals column.

I retain all rights to the document. You are free to make personal use of a copy of the document. No commercial use of this is allowed without permission.

Below are some details about the document and how to use it.

The Google Docs Spreadsheet is presented in view mode to anyone with the link. You can copy the document to your own Google Docs and begin test driving it. You only need to add your daily mileage under the appropriate dates (except columns C and D on the '2014' tab where you'll enter some historical data if you have it). All the rest is automatic.

Take a look at the two tabs, '2014' and '2015', that come with the initial version of the document. Both tabs will initially have all the formulas and functions active, but no data has been entered. On the '2014' tab, find cell H4 (this is where your mileage for January 1, 2014 would go), and enter the value 52 there. Note that not only did the contents of that cell change, but also all of the following:

  1. the weekly total for that week (cell M4)
  2. the running totals for the year (columns N and X)
  3. cells A7, A8 (current month's tally and # of rides)
  4. values in column B (cumulative miles)
Note also that the shading of cell A5 is now Yellow, and A7 is now Green. In Column A, the name of the Month is highlighted when the total for that current month exceeds the total for the same month in the previous year. The total mileage for the month is highlighted green when it exceeds the previous best total for that month. In this case, the previous year's total for January (column c) and the historical best (column d) were both initially set to zero. Take a quick look at tab '2015' and you'll see that columns C and D have changed, with the data from tab '2014' carried over to tab '2015'. If you change those zeros on tab '2014' in cells c7 and d7 to 53, you'll see the highlighting in column A disappear and cells in columns C and D change on tab '2015'.

At the very bottom of the spreadsheet, there is a table and a chart which include data about weekly averages. When tab '2014' cell H4 was set to 52, you saw that the table had averages calculated through the end of the year and the chart showed the graphical representation of those averages. While cell H4 is 52, go to cell G4 (the last day of the previous year) and enter 10. If you look at the values in M4 and N4 (total for the current week and cumulative total respectively) you'll see that those still show 52. However, on the table of weekly averages beginning in cell B61, you'll see the averages start with 62 miles. It only makes sense that any given week for the weekly averages is comprised of 7 days, so we will count mileage from December 30th and 31st, 2013 for the first 'week' in the table and chart at the bottom of tab '2014'. For these weeks that split over two years, I include the 'week' in with the year which has the most days of that week, so for the last two days of 2013 and the first 5 days of 2014, the 'week' is included on tab '2014'.

Please feel free to comment. Thanks.

Comments

skiffrun said…
Good grief, Rob! I didn't realize you such a numbers geek. I'm supposedly an actuary, and ... you're too much!

...Martin

Popular posts from this blog

The fourth time is a charm? PBP 2019 part 1

"I'm cured!". This was a posting I made within hours of having finished Paris, Brest, Paris 2015, hinting that it would be my last PBP. BS was officially called within seconds of that post hitting the interwebs, and this was not the first time that I was wrong (nor I suspect the last time I will be wrong). It is impossible to separate my experiences of riding PBP 2019 from any of those of past editions and the reader will have to forgive those moments of personal context that will follow below.

For the number of roadblocks I faced during the four year run-up to PBP 2019, as things transpired I led a somewhat charmed life once landing in France this past August. On the day before my flight, I received an email from Ed Felker inviting me to join Mary and him on a shake down ride out to Chartres. I had heard Craig Robertson describe the sight of the cathedral up on it's hill as he and Lori Cherry approached by bike from the west on a pre-PBP ride in 2015, and it immedi…

The fourth time is a charm?, PBP 2019 part 4

Unlike the fog that descended on the landscape overnight on Monday, the fog forming on Thursday morning was mental. But the chill was real. One downside to leaving Mortagne is that so much of the early terrain is down hill, and the coldest part of night was taking hold on that terrain for both the 84 hour riders and many if not most of the 90 hour riders. In the dark, it seemed that so few riders had gotten on the road, but as the kilometers clicked off the frequency of passing riders increased and packs were again forming, and as the sky lightened there was again a steady stream of red tail lights ahead. Foggy headed and chilled by early morning air that was nudging 40F, many riders reacted with indecision when navigational decisions were to be made and a simple left turn on the route that wasn't within a village caught 95% of the riders and large groups would have to navigate a u-turn amid the chaotic scene.

At long last, the undulating landscape smoothed out on this segment an…

The fourth time is a charm? PBP 2019 part 2

For riders that take PBP in three parts, Loudeac is often the control that cleaves the route into those three parts: Part 1, start to Loudeac: ~275 miles; Part 2, Loudeac-Brest-Loudeac: ~210 miles; Part 3, Loudeac to the finish: ~275 miles. On paper Part 2 looks to be a cake walk with it being a big chunk shorter. Well. No. It isn't a cake walk. One reason is that this section just feels hillier. Way more hilly, and in fact just getting away from Loudeac requires climbing a series of big rollers. Ironically, Roc'h Trevezel, the major climb on the ride, the highest point on the ride, and the location that provides the greatest unobstructed vista is much easier than so many other, shorter climbs. But it is in Part 2 and a focal point of that part.


The band (Mary and Ed on the tandem, Jerry, Anson, Roy and Brian K.) are all together as we leave and negotiate those first big rollers. Overnight, fog and appeared and settled in the lower laying areas and the early hours after sunup…