Bigmark

I have taken some time to build a cycling spreadsheet using Excel. It is a blank yearly sheet broken down by months, with a stats page for the year. I couldn’t find one I liked, so I made my own. It has the date, ride time (broken into hours and minutes), distance traveled, riding level, and average mph. I don’t need to keep track of anything else, but it wouldn’t be hard to modify it if you would like.

If anyone wants a copy it is on my web page under spreadsheet. Don’t be too critical, I just wanted something simple that I could keep track of my rides. I like it, and I hope someone else can use it.

Also for GP, I have a weight loss tracker on my diet page.

Mentor58

Very nice spreadsheet. Does what it's designed to do, and looks good doing it.

Steve

islenska

hey man thanks alot. i will check it out!

Bigmark

Thanks to other Forum members, I fixed the spelling mistake, and re-published the sheet to my web page. I hope everyone likes it, and I appreciate the feedback.

Hickabod

I just finished reading Bicycling Magazine's Guide to Touring. It had a spreadsheet similar to the one you have. However they had a few other things that might help you track the progress (or lack thereof) of your cycling. I'll try to remember to bring it to work tomorrow so that I can post some of those differences.
From what I can remember, they seemed to have a line for each ride. Then there were columns for things like the weather, terrain, and general mood before and after a ride. They suggested that you could start to identify your strong points after using it for a while. Though I suppose this is more for planning a tour, but it still might be helpful.
Once you get your spreadsheet filled up, perhaps you can wow us with some pie-charts.

landstander

It looks very nice to me! I do have a couple of thoughts, tho...

1. On the 2nd column header of the monthly tabs, you probably meant "Route" (a road, path, or course of travel) rather than "Rout" (an overwhelming defeat).
2. For folks with multiple bikes (we've got a lot around here, it might be worth adding a column to indicate which was used for a given ride. It wouldn't necessarily have to be referenced by the stats page, however.

Okay, enough nit-picking...

Bigmark

If you can, send me an e-mail with an attachment, or just post a copy so we can all see.

What killed me was figuring how to do the hour and minute calculations.
Once I got that, I wanted to start using it. I only have one bike (so far), but it wouldn’t take anything to add bikes, and other conditions. I will take any suggestions, and try to integrate them into my updated sheet.

The cool thing about spreadsheets is how easy it is to add things to them.

Bigmark

1. Spelling is not one of my strong points. I will fix that today.
2. Since I only have one bike (so far) I only had the need for a one bike sheet. It wouldn’t take much to add a column for multiple bikes.

cryogenic

at the top of each month, you've spelled riding incorrectly.. you typed "ridding". Everywhere else it's perfectly fine. Weird.

billwatson58

I've been using www.bikejournal.com and find it to be very useful

Bigmark

OK, I just updated the spreadsheet with the spelling repairs. Keep the suggestions coming.

Paiyili

Nice work. I, too, did an Excel spreadsheet as a bike journal, primarily as a project with which to play with Excel). Here it is. Has anybody else done this? I have also used the Cyclist Log from www.fitnesslogs.com.

Bigmark

That also looks good. I see you had the same problem with time as I did. Boy Excel does not make it easy to sum hours and minutes. I also checked out Cyclist Log, I would love to have something that worked well with my palm pilot, but that will be down the road a bit. Ha Ha.

digger

Jeez, those ARE good spreadsheets! I also have one but I am having a hellva time figuring out how to sum the time, can't seem to figure it out.

I'll have to take a harder look at what you guys did a little later.

Here's mine in Kms by the way, but not as nice as you guys, wow! I'm impressed!
https://www.accesswave.ca/~elvisbird/dougrides.XLS

Bigmark

Figuring time is a bugger.
If you want, drop me an e~mail and I will try to help you out. I broke my sheet into months, because I want to eventually have graphs and eye candy for each month. Maybe someday I will finish playing with it, but it makes the lunch hour go by.

Peek the Geek

Bigmark,

Tried making my own log in the past, and I also had trouble summing up a total time. Since it seems to be a common problem, could you or someone else post something on the subject?

Also, what do you use the "Level" column for? Thanks.

digger

Excellent idea, so we can all benefit. So how 'bout it bigmark? How does one total the times in the format 1:20:40 (hh:mm:ss)? I tried looking at one of the cells but it does not display the formula, and the Excel book I have and the softwre help does not make sense (to me).

Digger

toomanybikes

I always enter time in total minutes. Then you can use a formula to convert to hours and minutes if you wish. I don't convert to hours until month end, then only for display purposes.

For calculating average - time in minutes is just as useful if not more so.

CdCf

I know of one way to sum up time pretty easily.
Let's say you enter time as a decimal number (ignore seconds, or round off to nearest minute) in each cell, in column G (in this example - change as needed).
Then add another column, where you enter:

=INT(G4)+((G4-INT(E4))*100/60)

So, column G, with a time entry of 12 hours and 40 minutes, entered as 12.40, will convert to 12.67 in the column with my formula. And that time format will sum up nicely at the end of the sheet.

A backwards operation can then be added to convert the summed time to hours and minutes. Let's say the column with the above formula is column K, and the rows range from 1 to 50, then:

=((SUM(K1:K50)-INT(SUM(K1:K50)))*60/100)+INT(SUM(K1:K50))

This formula will change a decimal time back to an hours and minutes time (although still as a decimal number!!!).

Modify as required.

If you don't get it, just ask.

toomanybikes

The log I use is attached here.

It is one page, rather than separate sheets, and accumulates for multiple bikes, subtotals by month, by bike, by distance time and climb

It also real time graphs as you enter data.

If interested.

digger

Yeah, I understand and is something I was toying with when I started creating my own spreadsheet. Essentially it is just a 'regular' number with a decimal place. But I am fussy and wanted hh:mm:ss or at least hh:mm.

I think I might do as Toomanybikes suggested and use minutes instead, that way I can convert at months end for display purposes and graphs.

I'll have to look at what Bigmark has in his latest post also and maybe modify mine with his formulas. Although I WILL try yours as well just to learn from it and see what works best for me.

Thanks!
Digger

Bigmark

OK, grab your pencils, because here is the formula for ridding time. This is the formula that I used to accumulate the total monthly time. It took me a month to make it work the right way.

So in my spreadsheet the hours and minutes are broken up. THIS IS VERY IMPORTANT for Excel, since it does not understand time in the conventional sense.

Column C = Hours
Column D = Minutes
And I don’t use seconds. (My cyclometer has them, but I wanted to simplify the sheet.)

Formula:

=(SUM(D7:D70))/(24*60)+(SUM(C7:C70)/24)

Format the cell as follows, to tell Excel how to deal with the numbers.
CUSTOM [h]:mm

Now I will get long winded, and explain my formula.

(SUM(D7:D70))/(24*60)
This sums up all the minutes in the column, then divides it by how many hours in a day multiplied by how many minutes in an hour. This will turn the minutes into a fraction that Excel knows how to handle.

(SUM(C7:C70)/24)
This just adds up the hours, and divides them by 24 to put them into a fraction Excel understands.

Now when you format the cell with
CUSTOM setting, scroll down and find [h]:mm
This takes the above numbers and puts them back into time.


Now for the Average MPH on each line,
This is a bit easer, but still a bear to figure out.
Average MPH is Distance/Time. Well time is not easy in Excel as we showed above, but with this formula it works.

For my spreadsheet
C = Hours
D = Minutes
E = Distance

(E7/(((D7/(24*60))+(C7/24))*24)))

Now the ((D7/(24*60))+(C7/24))*24)) takes time Ex. 1hour 30minutes and makes it decimal that excel understands 1.5 then the (E7/ divides it by the distance traveled to give you the average MPH.

Now format the cell for Number, with 2 decimal places.

That is all there is to it. Once I realized to break the hours and minutes up the rest fell into place. It looks complicated, but it is easy once you apply it to your sheet.

I hope this helps, and again this is just how I did it, I am sure there are a lot more ways to do time, I am just not good enough at math or excel to figure them out.

As for level, I use this for my ride intensity from 1 through 10.

Paiyili

Interesting, I'll take a run at it. Thanks.

neilG

Isn't there a way I can change the decimal place to put minutes in as decimals? Like 1 minute 30 seconds entered as 1.5 minutes. I work hard for those fractions! Excellent spreadsheet!

Bigmark

Yea, if you want to put the numbers in as a fraction of an hour, just remember this.
1hour 15minutes would be 1.25, and 1hour 35minutes would be 1.5833
That is what I was trying to get away from. I just wanted to put in the actual ride time, and let Bill Gates do the math. If you put it in as a decimal you will still have to divide it by 24, and format the cell to [h]:mm in order to get your actual ride time. If you don’t do this you will have time in base 10, and no mater what, the metric system does not work for time.

What I did was the inverse of this, I put time in as hour and minutes, and had brother Bill convert that to a fraction, then I added all the fractions, then I had Bill turn the fractions back into time. ~ What a pain in the @r$$.