Advertise on Bikeforums.net



User Tag List

Results 1 to 12 of 12
  1. #1
    Disco Infiltrator Darth Lefty's Avatar
    Join Date
    May 2013
    Location
    Orangevale CA
    My Bikes
    76 Paramount, 02 Hardrock, 72 Super Sport
    Posts
    1,514
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Cassette builder spreadsheet

    I made this spreadsheet to calculate the cog sequence for a cassette. It uses the (speeds-1)th root of the ratio from the top to bottom cog and finds integer cog steps to each next gear. The equations are in a BBCode table below and you should be able to paste it straight into the upper left corner of an Excel worksheet.

    Feel free to bug check, play, find objections, make improvements. I originally had this starting with the small cog but it sometimes missed by a tooth the final cog.

    Cassette builder
    # cogs Top Bottom
    8 x 11 to 28
    ideal ratio =(E3/C3)^(1/(A3-1))
    gear ideal step cog
    1 =IF(A8<=$A$3,$C$3*$B$5^($A$3-A8),FALSE) =B8
    =A8+1 =IF(A9<=$A$3,$C$3*$B$5^($A$3-A9),FALSE) =IF(B9,ROUND(B9-B8,0)) =IF(B9,C9+D8) =IF(B9,D8/D9)
    =A9+1 =IF(A10<=$A$3,$C$3*$B$5^($A$3-A10),FALSE) =IF(B10,ROUND(B10-B9,0)) =IF(B10,C10+D9) =IF(B10,D9/D10)
    =A10+1 =IF(A11<=$A$3,$C$3*$B$5^($A$3-A11),FALSE) =IF(B11,ROUND(B11-B10,0)) =IF(B11,C11+D10) =IF(B11,D10/D11)
    =A11+1 =IF(A12<=$A$3,$C$3*$B$5^($A$3-A12),FALSE) =IF(B12,ROUND(B12-B11,0)) =IF(B12,C12+D11) =IF(B12,D11/D12)
    =A12+1 =IF(A13<=$A$3,$C$3*$B$5^($A$3-A13),FALSE) =IF(B13,ROUND(B13-B12,0)) =IF(B13,C13+D12) =IF(B13,D12/D13)
    =A13+1 =IF(A14<=$A$3,$C$3*$B$5^($A$3-A14),FALSE) =IF(B14,ROUND(B14-B13,0)) =IF(B14,C14+D13) =IF(B14,D13/D14)
    =A14+1 =IF(A15<=$A$3,$C$3*$B$5^($A$3-A15),FALSE) =IF(B15,ROUND(B15-B14,0)) =IF(B15,C15+D14) =IF(B15,D14/D15)
    =A15+1 =IF(A16<=$A$3,$C$3*$B$5^($A$3-A16),FALSE) =IF(B16,ROUND(B16-B15,0)) =IF(B16,C16+D15) =IF(B16,D15/D16)
    =A16+1 =IF(A17<=$A$3,$C$3*$B$5^($A$3-A17),FALSE) =IF(B17,ROUND(B17-B16,0)) =IF(B17,C17+D16) =IF(B17,D16/D17)
    =A17+1 =IF(A18<=$A$3,$C$3*$B$5^($A$3-A18),FALSE) =IF(B18,ROUND(B18-B17,0)) =IF(B18,C18+D17) =IF(B18,D17/D18)
    Last edited by Darth Lefty; 08-19-14 at 01:13 AM.

  2. #2
    Disco Infiltrator Darth Lefty's Avatar
    Join Date
    May 2013
    Location
    Orangevale CA
    My Bikes
    76 Paramount, 02 Hardrock, 72 Super Sport
    Posts
    1,514
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)
    Here are some funny things I've discovered using this. The sequence this gives is the closest to an exponential line with equal ratio steps. But this is not always the cassette you can buy, and the reason seems to be that there's a more common sequence available. For instance in the example in the first post, an 11-28 cassette commonly found on 24-speed mountain bikes and hybrids usually goes,

    11-13-15-17-19-21-24-28

    But the spreadsheet shows a sweeter sequence.

    11-13-15-17-19-22-25-28

    However this sequence does not use the 21-24-28 combination which is found in a lot of cassettes.

    You can also see that 8x11-30 (and 9x11-34) is really sweet... but 8x11-32 has 21-24-28-32.

    25 and 27 basically only ever appear as a bottom gear and you'll never see an odd gear higher.
    Last edited by Darth Lefty; 08-19-14 at 12:32 AM.

  3. #3
    Senior Member
    Join Date
    Sep 2013
    Posts
    562
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    The "sweeter" sequence is subjective and down to personal taste, I'd rather have the 2 tooth difference of a 19-21 step. I'm always switching between those two gears and wish I could find a 10 speed cassette with a 20 tooth cog.

    FWIW Campy makes 13-29 road cassettes if you want to throw that in the mix.

  4. #4
    Disco Infiltrator Darth Lefty's Avatar
    Join Date
    May 2013
    Location
    Orangevale CA
    My Bikes
    76 Paramount, 02 Hardrock, 72 Super Sport
    Posts
    1,514
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)
    Well, sweeter in that it follows the curve more closely. I wouldn't try to use this to argue with preference.

  5. #5
    Senior Member trailangel's Avatar
    Join Date
    Feb 2014
    Location
    Pasadena, CA
    My Bikes
    SOMA ES, MEDICI PRO STRADA, JAMIS CODA COMP
    Posts
    283
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    IMHO you are better off looking at gear inches than ratios.
    There are not any sweet gears.

  6. #6
    Disco Infiltrator Darth Lefty's Avatar
    Join Date
    May 2013
    Location
    Orangevale CA
    My Bikes
    76 Paramount, 02 Hardrock, 72 Super Sport
    Posts
    1,514
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by trailangel View Post
    IMHO you are better off looking at gear inches than ratios.
    There are not any sweet gears.
    This will do gear inches but it's fairly trivial, I wanted to share something clever.

    Edit - sigh - replace all smileys with colon D.
    Tire size 700c x 32mm
    rim mm 622 630 (27), 622 (700c), 559 (26 & fat)
    tire mm 32 25 (1), 28 (1.125), 32 (1.25), 38 (1.5), 41, 45 (1.75), 51 (2), 54(2.125), 76 (3), 96.5 (3.8), 122 (4.8)
    tire dia =(B2+2*B3)/25.4
    top bottom
    ring steps =LN(F8)/LN(D25) =LN(G8)/LN(D25)
    ratio of rings =E10/F10 =F10/G10
    2-step ratio 1-step ratio gear 3rd 2nd 1st
    road triple 52 39 30
    8x11 to 30
    11 no gear =IF($D12="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D12*E$10*$B$4)) =IF($D12="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D12*F$10*$B$4)) =IF($D12="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D12*G$10*$B$4))
    =IF(D12="no gear","",D14/D12) =IF(D12="no gear","",D13/D12) 10 no gear =IF($D13="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D13*E$10*$B$4)) =IF($D13="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D13*F$10*$B$4)) =IF($D13="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D13*G$10*$B$4)) high =MAX(E11:G22)
    =IF(D13="no gear","",D15/D13) =IF(D13="no gear","",D14/D13) 9 no gear =IF($D14="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D14*E$10*$B$4)) =IF($D14="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D14*F$10*$B$4)) =IF($D14="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D14*G$10*$B$4)) low =MIN(E11:G22)
    =IF(D14="no gear","",D16/D14) =IF(D14="no gear","",D15/D14) 8 11 =IF($D15="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D15*E$10*$B$4)) =IF($D15="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D15*F$10*$B$4)) =IF($D15="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D15*G$10*$B$4)) overall ratio =I13/I14
    =IF(D15="no gear","",D17/D15) =IF(D15="no gear","",D16/D15) 7 13 =IF($D16="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D16*E$10*$B$4)) =IF($D16="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D16*F$10*$B$4)) =IF($D16="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D16*G$10*$B$4)) chain wrap =MAX(E10:G10)-MIN(E10:G10)+MAX(D1222)-MIN(D1222)
    =IF(D16="no gear","",D18/D16) =IF(D16="no gear","",D17/D16) 6 15 =IF($D17="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D17*E$10*$B$4)) =IF($D17="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D17*F$10*$B$4)) =IF($D17="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D17*G$10*$B$4)) RD max cog =MAX(D1222)
    =IF(D17="no gear","",D19/D17) =IF(D17="no gear","",D18/D17) 5 17 =IF($D18="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D18*E$10*$B$4)) =IF($D18="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D18*F$10*$B$4)) =IF($D18="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D18*G$10*$B$4)) FD diff =MAX(E10:G10)-MIN(E10:G10)
    =IF(D18="no gear","",D20/D18) =IF(D18="no gear","",D19/D18) 4 20 =IF($D19="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D19*E$10*$B$4)) =IF($D19="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D19*F$10*$B$4)) =IF($D19="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D19*G$10*$B$4))
    =IF(D19="no gear","",D21/D19) =IF(D19="no gear","",D20/D19) 3 23 =IF($D20="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D20*E$10*$B$4)) =IF($D20="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D20*F$10*$B$4)) =IF($D20="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D20*G$10*$B$4))
    =IF(D20="no gear","",D22/D20) =IF(D20="no gear","",D21/D20) 2 26 =IF($D21="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D21*E$10*$B$4)) =IF($D21="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D21*F$10*$B$4)) =IF($D21="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D21*G$10*$B$4))
    =IF(D21="no gear","",D22/D21) 1 30 =IF($D22="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D22*E$10*$B$4)) =IF($D22="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D22*F$10*$B$4)) =IF($D22="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D22*G$10*$B$4))
    cog ratio =D22/MIN(D1221)
    # gears =COUNT(B13:B22)+1
    one step =(MAX(D1222)/MIN(D1222))^(1/(D24-1))
    half step =SQRT(D25)
    1.5 step =D26^3
    2 step =D25^2
    3 step =D27^2

  7. #7
    7-speed doomsday prepper ThermionicScott's Avatar
    Join Date
    Aug 2011
    Location
    CID
    My Bikes
    1991 Bianchi Eros, 1964 Armstrong, 1988 Diamondback Ascent, 1988 Bianchi Premio, 1987 Bianchi Sport SX
    Posts
    9,127
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)
    Hey @Darth Lefty, I've done a very similar thing, although using a different approach. You can't be too OCD when going down this path or the deviations from the "ideal" progression (done to limit manufacturing scope, I'm sure) will drive you nuts! For instance, once I had played around with this for a while, I wished that my 13-15-17-19-21-24-28 cassettes were 13-15-17-19-22-25-28.

    What I did was to fire up an Excel spreadsheet with the starting cog and a certain percentage over 100% (ex: 1.1 for ~10% jumps) in the top two cells. Every following cog is the rounded version of prior one multiplied by that percentage. From there, you can tweak the percentage and copy cells until you have the cassette size and range you want.



    (I'm not an Excel "power user" so it had to be simple. )
    Last edited by ThermionicScott; 08-19-14 at 11:25 AM.
    Quote Originally Posted by chandltp View Post
    There's no such thing as too far.. just lack of time
    RUSA #7498

  8. #8
    Senior Member Bill Kapaun's Avatar
    Join Date
    Feb 2007
    Location
    Mid Willamette Valley, Orygun
    My Bikes
    86 RockHopper,2008 Specialized Globe. Both upgraded to 9 speeds.
    Posts
    7,504
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I've always plotted my gears on a spreadsheet.
    In the end, I swap in the cogs my legs & lungs like.
    In cold weather where I'm bundled up and my conditioning is at its minimum, I use lower gears than warm weather where I'm wearing shorts and my conditioning is better.

  9. #9
    Passista Reynolds's Avatar
    Join Date
    Jul 2005
    My Bikes
    1998 Pinarello Asolo, 1992 KHS Montaña pro, 1980 Raleigh DL-1, IGH Hybrid, IGH Utility
    Posts
    4,665
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    My "rule" is trying to get about 10% jumps.

  10. #10
    Disco Infiltrator Darth Lefty's Avatar
    Join Date
    May 2013
    Location
    Orangevale CA
    My Bikes
    76 Paramount, 02 Hardrock, 72 Super Sport
    Posts
    1,514
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)
    @ThermionicScott that works! It seems that once out of the corncob section most cassettes available go about 1.15:1, and this makes a good double shift with 52-39 or 42-32 rings. Some are more clever, especially with fewer gears. 5-speed touring freewheels (5x14-32) were about 1.23:1. The SRAM 7-speed 12-32 MTB cassette is 1.15 for the top four shifts and 1.23 for the bottom two.

  11. #11
    Senior Member Dfrost's Avatar
    Join Date
    May 2013
    Location
    Pacific Northwest
    My Bikes
    '87 Marinoni, Rivendell Rambouillet, '79 Miyata 912, Wife's '97 Erickson
    Posts
    477
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    I find that bigger steps work fine on the ends of the cassette, but a single tooth step can be valuable in the middle, assuming that the middle of the cassette has the "flat road" gears.

    And I suspect that you will have a hard time finding 22t cogs.

  12. #12
    Ride More seedsbelize's Avatar
    Join Date
    Nov 2010
    Location
    Tixkokob, Yucatán, México
    My Bikes
    '84 Schwinn Letour Luxe, '84 Schwinn traveler, '87 Schwinn Prelude, 91 Giant Yukon, 92 Schwinn Paramount PDG 5 unicrown
    Posts
    1,970
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    It's all Greek to me.

    Life is is too short to care what others think of your bike.

Posting Permissions

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