Cycling and bicycle discussion forums. 
   Click here to join our community Log in to access your Control Panel  


Go Back   > >

General Cycling Discussion Have a cycling related question or comment that doesn't fit in one of the other specialty forums? Drop on in and post in here! When possible, please select the forum above that most fits your post!

User Tag List

Reply
 
Thread Tools Search this Thread
Old 07-05-05, 10:31 AM   #1
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Excel Spreadsheet

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.
Bigmark is offline   Reply With Quote
Old 07-05-05, 10:44 AM   #2
Mentor58
'Mizer Cats are INSANE
 
Mentor58's Avatar
 
Join Date: May 2005
Location: Clarksville, TN
Bikes: C-dale T800
Posts: 808
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Very nice spreadsheet. Does what it's designed to do, and looks good doing it.

Steve
Mentor58 is offline   Reply With Quote
Old 07-05-05, 11:31 AM   #3
islenska
Senior Member
 
Join Date: Oct 2004
Bikes:
Posts: 211
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
hey man thanks alot. i will check it out!
islenska is offline   Reply With Quote
Old 07-05-05, 12:00 PM   #4
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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.
Bigmark is offline   Reply With Quote
Old 07-05-05, 12:29 PM   #5
Hickabod
Dude On Bike
 
Hickabod's Avatar
 
Join Date: Mar 2005
Location: Spartanburg, SC
Bikes: Raliegh C40 Hybrid Bike. Cannondale R900 Triple. Schwinn World - Fixie Conversion
Posts: 216
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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.
Hickabod is offline   Reply With Quote
Old 07-05-05, 12:38 PM   #6
landstander
darling no baka
 
landstander's Avatar
 
Join Date: May 2005
Location: I come from a place where the nuts hunt the squirrels
Bikes: Bike Friday New World Tourist, 2005 Trek 520, 2005 Raleigh Companion
Posts: 437
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by 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.
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...
__________________
Dragon... ATTACK!
landstander is offline   Reply With Quote
Old 07-06-05, 04:57 AM   #7
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
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.
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 is offline   Reply With Quote
Old 07-06-05, 05:05 AM   #8
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
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).
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.
  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.
Bigmark is offline   Reply With Quote
Old 07-06-05, 05:13 AM   #9
cryogenic
Campy or bust :p
 
cryogenic's Avatar
 
Join Date: Oct 2004
Location: Knoxville, TN
Bikes: Surly Karate Monkey commuter build
Posts: 3,139
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
at the top of each month, you've spelled riding incorrectly.. you typed "ridding". Everywhere else it's perfectly fine. Weird.
cryogenic is offline   Reply With Quote
Old 07-06-05, 05:23 AM   #10
billwatson58
Planet Saver
 
billwatson58's Avatar
 
Join Date: Jul 2002
Location: Near western burb of Chicago
Bikes: Cannondale CAAD-10, Novara Randonee, Raleigh Super Grand Prix, Schwinn Mirada Sport winter beater
Posts: 289
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
I've been using www.bikejournal.com and find it to be very useful
billwatson58 is offline   Reply With Quote
Old 07-06-05, 05:31 AM   #11
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
OK, I just updated the spreadsheet with the spelling repairs. Keep the suggestions coming.
Bigmark is offline   Reply With Quote
Old 07-06-05, 05:56 AM   #12
Paiyili
pAIYILI
 
Paiyili's Avatar
 
Join Date: Jun 2005
Location: Pennsylvania, PA USA
Bikes: Specialized Crosroads Sport (Hybrid)
Posts: 130
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by Bigmark
OK, I just updated the spreadsheet with the spelling repairs. Keep the suggestions coming.

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.
Paiyili is offline   Reply With Quote
Old 07-06-05, 06:45 AM   #13
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
I, too, did an Excel spreadsheet as a bike journal, primarily as a project with which to play with Excel
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.
Bigmark is offline   Reply With Quote
Old 07-06-05, 06:55 AM   #14
digger
Senior Member
 
digger's Avatar
 
Join Date: May 2002
Location: Nova Scotia Canada
Bikes: 2010 Cervelo RS. Marinoni touring bike. Kona Tanuki MTB. Specialized Hardrock (commuter). Raleigh (early 80s) Super Gran Prix.
Posts: 2,155
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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!
http://www.accesswave.ca/~elvisbird/dougrides.XLS
digger is offline   Reply With Quote
Old 07-06-05, 11:45 AM   #15
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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.
Bigmark is offline   Reply With Quote
Old 07-06-05, 03:07 PM   #16
Peek the Geek
is slower than you
 
Peek the Geek's Avatar
 
Join Date: Jan 2005
Location: WI
Bikes: Gunnar Sport, Marin Pine Mountain, Gunnar Ruffian, Gunnar Roadie, BMC Fourstroke, Salsa Vaya
Posts: 1,486
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by 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.
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.
__________________
Proud supporter of the Chippewa Off-Road Bike Association (CORBA)
www.chippewaoffroad.org

Peek the Geek is offline   Reply With Quote
Old 07-06-05, 04:53 PM   #17
digger
Senior Member
 
digger's Avatar
 
Join Date: May 2002
Location: Nova Scotia Canada
Bikes: 2010 Cervelo RS. Marinoni touring bike. Kona Tanuki MTB. Specialized Hardrock (commuter). Raleigh (early 80s) Super Gran Prix.
Posts: 2,155
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted 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?
Thanks.

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
digger is offline   Reply With Quote
Old 07-06-05, 06:36 PM   #18
toomanybikes
Senior Member
 
Join Date: Sep 2004
Location: Okanagan Valley, BC
Bikes: Too Many
Posts: 431
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted 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.
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.
toomanybikes is offline   Reply With Quote
Old 07-06-05, 06:57 PM   #19
CdCf
Videre non videri
 
Join Date: Sep 2004
Location: Gothenburg, Sweden
Bikes: 1 road bike (simple, light), 1 TT bike (could be more aero, could be lighter), 1 all-weather commuter and winter bike, 1 Monark 828E ergometer indoor bike
Posts: 3,208
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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.
CdCf is offline   Reply With Quote
Old 07-06-05, 08:26 PM   #20
toomanybikes
Senior Member
 
Join Date: Sep 2004
Location: Okanagan Valley, BC
Bikes: Too Many
Posts: 431
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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.

Last edited by toomanybikes; 07-06-05 at 08:27 PM. Reason: attached wrong file!!
toomanybikes is offline   Reply With Quote
Old 07-07-05, 05:07 AM   #21
digger
Senior Member
 
digger's Avatar
 
Join Date: May 2002
Location: Nova Scotia Canada
Bikes: 2010 Cervelo RS. Marinoni touring bike. Kona Tanuki MTB. Specialized Hardrock (commuter). Raleigh (early 80s) Super Gran Prix.
Posts: 2,155
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by 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.
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
digger is offline   Reply With Quote
Old 07-07-05, 05:20 AM   #22
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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.
Bigmark is offline   Reply With Quote
Old 07-07-05, 06:28 AM   #23
Paiyili
pAIYILI
 
Paiyili's Avatar
 
Join Date: Jun 2005
Location: Pennsylvania, PA USA
Bikes: Specialized Crosroads Sport (Hybrid)
Posts: 130
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
Originally Posted by Bigmark

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.
Interesting, I'll take a run at it. Thanks.
Paiyili is offline   Reply With Quote
Old 07-07-05, 05:59 PM   #24
neilG
Senior Member
 
neilG's Avatar
 
Join Date: May 2005
Location: Ventura, CA
Bikes: Madone, De Rosa, Langster, some old Brit track iron
Posts: 441
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
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!
neilG is offline   Reply With Quote
Old 07-08-05, 04:46 AM   #25
Bigmark
But Getting Smaller
Thread Starter
 
Bigmark's Avatar
 
Join Date: May 2005
Location: Boardman Ohio
Bikes: 05 Gary Fisher Tiburon S
Posts: 235
Mentioned: 0 Post(s)
Tagged: 0 Thread(s)
Quoted: 0 Post(s)
Quote:
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.
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$$.
Bigmark is offline   Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



All times are GMT -6. The time now is 03:24 PM.