Cycling Training Log Spreadsheet
Written by Michael on January 2nd, 2008
Since 1995, I’ve been tracking every mile I rode on the bike in an Excel spreadsheet. This has proven to be an excellent training tool and helped me notice trends and (sometimes) avoid over training. Five years ago I set out to find an automated way to compute my average speed and embarrassingly, I couldn’t figure out how to do this in Excel so I wrote my first JavaScript application and published it over at : http://www.raincitystory.com/mph.htm. Since that time, I’ve averaged almost 10,000 hits per month on that page (the other pages on my entire site get less than 1,000 hits per month COMBINED) and I often get e-mails from people asking for more help to automate the aggregation of their training data.
Since I started 12 years ago, the log has gone through some serious evolution and I now think it’s ready from prime time. Here are some major features:
1) All days including leap years for 2008 through 2012 are accounted for
2) Average speed automatically calculated and summed on a daily, monthly and annual basis
3) Space to record and average body weight, average heart rate, maximum heart rate, calories burned, temperature, elevation gain, cadence and time of day
4) Automatic monthly summary of number of rides, time spent on the bike, distance, and average speed
5) GRAPHS!
6) Much, much more
License: GPL v2 (improvements appreciated!)
Available Formats:
Microsoft Excel 2000/2003/2007
Open Office Calc
Google Docs (recommended so you don’t have to worry about backing up! * Be sure to copy to your own Google account)
I’ve put a sample entry in the first row so please be sure to delete that when using. Any columns and rows can be deleted so enjoy!
4
AM
Darn - are you ‘good’ or what, I’m not a cycler but I’m still impressed!!
I love the deer photos, so sweet - how could anyone ‘take down’ such cute critters!? Were they dining on your shrubs?? The deer have destroyed some of our trees, but mostly from rutting, thusly destroying the bark of the trunk of some smaller trees. Daisy gets so wound up when she sees them (basically every day!!) she barks and rings her bell to go outside and run the deer - drives me crazy!!
Nance
5
PM
Like the sheet , Couple of questions:
Calories? how do we determine that?
I ride both Road and Mountain. Any thoughts on a way other than having two sheets to calculate both?
Thanks
5
PM
Thanks for the feedback John and the calorie problem will be my next addition to the spreadsheet. I’ve researched this problem a lot and there’s no foolproof way of determining calories burned during a workout- there are just too many variables.
I calculate mine using my age, body weight, average heart rate during the workout, and the duration of the workout in minutes.
Formula:
kCal = (-55.0969 + 0.2017 * age + 0.1988 * weight + 0.6309 * heartrate)/4.184
I got this formula from a Professor in Biology at a college in Seattle and I honestly don’t know how accurate it is. The numbers make sense though and as long as I use it ride after ride, there will be consistency and the data will be “good enough” to use.
As for the road and mountain bike question, I don’t think it matters (though I’ll say that mountain biking feels a lot harder than the road!).
1
AM
This is just what I’ve been looking for. Thanks for sharing your work with spreadsheet-challenged folks like me. Do you have anything from prior years you could share? I’d like to enter old data to compare years. Thanks again!!
11
AM
Thanks so much for taking the time to develop the spreadsheet. I was going to draw one up this weekend, but you have saved me the effort. Now I have more time to start training!
2
PM
If you modify the formula in the average speed column to read:
=IF(D2=0,”",E2/(D2*24))
the unsightly #DIV/O! will go away.
Otherwise, nice SS. Similar to one I came up with to track my rides. Mine has some bike maintenance reminders in it also.