Friday, January 15, 2021

Head Over Wheels with Excel

 

Recently I completed an online Excel class to learn the basics. I've used and referenced spreadsheets, but had never learned how to create one from scratch - that you can style them, tickled me to no end!

I have collected daily mileage on paper calendars for many years, just because it's fun to see where I'm at from year to year, and easy to tally mileage with a calculator. Generally, I hover between 2700-3000 miles. I don't use a bike computer (and don't want one) - If I cannot reasonably guess mileage then I reference Google Timeline for a ball park figure, then tack on a mile because of it's less than perfect tracking. I also ride 5 different bicycles, so this system works fine for me.

I thought it would be fun to analyze a few metrics: day of week totals, monthly totals, and cumulative annual mileage - that's all I really care about. I required my data on one spreadsheet for simplicity, and in a reusable format. I adapted my own design after inspecting very detailed options on the Internet - and there were many! I have two sheets: one for data, one for charts. In future years, I will adjust each monthly data column so they align with correct day of the week. 

2020 wasn't a normal year (that's an understatement) as I only commuted to work January - March, but managed decent mileage overall. It's interesting that Fridays were the highest mileage days; I have no clue why. I suspect that in future Monday-Friday workday life, mid-week mileage will even out. Also, I expect an uptick in winter mileage due to using studded bike tires. I plan to continue documenting riding miles the old fashioned way in 2021, then transferring those miles periodically to the spreadsheet.

Do you use a spreadsheet to track your cycling miles? If so, what stats interest you?

8 comments:

  1. I have not learned to use a spread sheet, and think that is a great idea. What plan did you use online to learn, and would you recommend that one?

    Your charts and graphs look great! Nice mileage.

    ReplyDelete
    Replies
    1. I completed an online class at a community college. Perhaps there's a You tube channel that provides the same information?

      Delete
  2. Oooo, I love the days of the week mileage chart! Fun! I use Excel but for work (and coffeeneuring!). For bike miles and such, I use a paper log and Garmin Connect (and use the analytics from Connect). It's just easiest for me, as I'm terrible about remembering to write things down.

    ReplyDelete
    Replies
    1. Thanks and great to hear about Garmin. I know there are so many ways to track mileage.

      Delete
  3. For anyone that's interested, I'm willing to share my Excel file. See my About page/tab for email address.

    ReplyDelete
  4. I use Strava, the free version, and Google Timeline. Strava suits me, I'm not interested in times and competition just the routes ridden, although I like to see the total elevation and also you can analyse it to give you a ride profile. I export the routes onto my Ordnance Survey maps on the PC as I prefer to view them there, and I can save them as gpx. files . Also cycling friends also use Strava, and as you can only exercise alone in this lockdown its interesting to see where they have been, and you can copy their routes.
    As well as a yearly summary it gives you the mileage for each bike used, this is handy especially when replacing things like chains and brake pads as you can see how long they have lasted.
    This year I did more miles on the bikes, 5000, than I did in the car.

    ReplyDelete
  5. I keep track of miles, hours ridden, Avg. speed, Calories burned and Elevation Gain by using the MapmyFitness App on the phone, then input the stats on BikeJournal.com I do have a bike computer but I use it mainly for "Actual TIME" and as a "back up" if the phone has a problem (Computer always gives me 1 more mile distance than the phone app?). Out of habit I keep the mileage in a handwritten notebook also, LOL don't need to be I can't seem to give it up! ;)

    ReplyDelete
  6. I do keep a spreadsheet and have done so for the last ten years. I am sorry to say that the results show a steady decline in average speed which more or less exactly matches my increasing years. This may be inevitable but it is still annoying. Indeed, it took a bit of pleasure off my riding two years ago but fortunately, I came to terms with it this past year and enjoyed my outings a lot more.

    I like your handsome colour schemes a lot. I have not thought of analyzing my rides by day of the week.

    ReplyDelete

Due to increased Spam, I am moderating comments. Thank you for your patience.