Advertise on Bikeforums.net



User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 40
  1. #1
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

  2. #2
    'Mizer Cats are INSANE Mentor58's Avatar
    Join Date
    May 2005
    Location
    Clarksville, TN
    My Bikes
    C-dale T800
    Posts
    808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Very nice spreadsheet. Does what it's designed to do, and looks good doing it.

    Steve

  3. #3
    Senior Member
    Join Date
    Oct 2004
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey man thanks alot. i will check it out!

  4. #4
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

  5. #5
    Dude On Bike Hickabod's Avatar
    Join Date
    Mar 2005
    Location
    Spartanburg, SC
    My Bikes
    Raliegh C40 Hybrid Bike. Cannondale R900 Triple. Schwinn World - Fixie Conversion
    Posts
    216
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.

  6. #6
    darling no baka landstander's Avatar
    Join Date
    May 2005
    Location
    I come from a place where the nuts hunt the squirrels
    My Bikes
    Bike Friday New World Tourist, 2005 Trek 520, 2005 Raleigh Companion
    Posts
    437
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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!

  7. #7
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

  8. #8
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

  9. #9
    Campy or bust :p cryogenic's Avatar
    Join Date
    Oct 2004
    Location
    Knoxville, TN
    My Bikes
    Surly Karate Monkey commuter build
    Posts
    3,139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    at the top of each month, you've spelled riding incorrectly.. you typed "ridding". Everywhere else it's perfectly fine. Weird.

  10. #10
    Planet Saver billwatson58's Avatar
    Join Date
    Jul 2002
    Location
    Near western burb of Chicago
    My Bikes
    a few: Serotta, Fuji(2), Raleigh(4), Schwinn(2) to name a few
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been using www.bikejournal.com and find it to be very useful
    Bikes are fun. And better.

  11. #11
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I just updated the spreadsheet with the spelling repairs. Keep the suggestions coming.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

  12. #12
    pAIYILI Paiyili's Avatar
    Join Date
    Jun 2005
    Location
    Pennsylvania, PA USA
    My Bikes
    Specialized Crosroads Sport (Hybrid)
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.
    Regards,
    Paiyili
    Windows Warrior Homepage

  13. #13
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

  14. #14
    Senior Member digger's Avatar
    Join Date
    May 2002
    Location
    Nova Scotia Canada
    My Bikes
    Cervelo RS. Marinoni touring bike. Kona Tanuki. Specialized Harrock (commuter). Raleigh (early 80s) Super Gran Prix.
    Posts
    1,980
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(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

  15. #15
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

  16. #16
    is slower than you Peek the Geek's Avatar
    Join Date
    Jan 2005
    Location
    WI
    My 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)
    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


  17. #17
    Senior Member digger's Avatar
    Join Date
    May 2002
    Location
    Nova Scotia Canada
    My Bikes
    Cervelo RS. Marinoni touring bike. Kona Tanuki. Specialized Harrock (commuter). Raleigh (early 80s) Super Gran Prix.
    Posts
    1,980
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(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

  18. #18
    Senior Member
    Join Date
    Sep 2004
    Location
    Okanagan Valley, BC
    My Bikes
    Too Many
    Posts
    431
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(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.

  19. #19
    Videre non videri
    Join Date
    Sep 2004
    Location
    Gothenburg, Sweden
    My 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,204
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.

  20. #20
    Senior Member
    Join Date
    Sep 2004
    Location
    Okanagan Valley, BC
    My Bikes
    Too Many
    Posts
    431
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(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 09:27 PM. Reason: attached wrong file!!

  21. #21
    Senior Member digger's Avatar
    Join Date
    May 2002
    Location
    Nova Scotia Canada
    My Bikes
    Cervelo RS. Marinoni touring bike. Kona Tanuki. Specialized Harrock (commuter). Raleigh (early 80s) Super Gran Prix.
    Posts
    1,980
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(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

  22. #22
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

  23. #23
    pAIYILI Paiyili's Avatar
    Join Date
    Jun 2005
    Location
    Pennsylvania, PA USA
    My Bikes
    Specialized Crosroads Sport (Hybrid)
    Posts
    130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.
    Regards,
    Paiyili
    Windows Warrior Homepage

  24. #24
    Senior Member
    Join Date
    May 2005
    Location
    Ventura, CA
    My Bikes
    Dean, De Rosa, Langster, some old Brit track iron
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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!

  25. #25
    But Getting Smaller Bigmark's Avatar
    Join Date
    May 2005
    Location
    Boardman Ohio
    My Bikes
    05 Gary Fisher Tiburon S
    Posts
    235
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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.
    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$$.
    ~~"Get on your bikes and ride!"~~
    Working to be JustMark

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •