Cycling Training Log Spreadsheet
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!
January 4th, 2008 - 08:22
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
January 5th, 2008 - 16:22
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
January 5th, 2008 - 17:45
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!).
March 1st, 2008 - 08:34
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!!
July 11th, 2008 - 06:29
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!
November 2nd, 2008 - 18:23
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.
March 13th, 2009 - 10:36
Mike, I tried this formula in open office calc and it returns an error. Any suggestions on the correct formula for calc?
Nice spreadsheet, very easy to use and lots of info.
April 19th, 2009 - 15:50
Try this in openoffice;
=IF(D2=0;0; IF(D2=”";0;E2/(D2*24)))
February 26th, 2010 - 07:26
Thank you for your work. I have tried to use the formula for calculating kcal. and I get a result of 21. Is the formula correct?
Thanks again
May 3rd, 2010 - 12:38
Hi. Like the spreadsheet. Is it ok if I do some work on it? I work with excel everyday and there are some things that could be improved.
Let me know.
May 3rd, 2010 - 14:01
Sure Eric, thanks!
June 7th, 2010 - 18:24
anyone else notice that all the dates are 2008? Even the 09/10/11/12 years etc.
Anyway to fix this? I suck with Excel