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.

[table="width: 500, class: grid"]
[TR]
[TD]Cassette builder[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]# cogs[/TD]
[TD][/TD]
[TD]Top[/TD]
[TD][/TD]
[TD]Bottom[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]x[/TD]
[TD]11[/TD]
[TD]to[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ideal ratio[/TD]
[TD]=(E3/C3)^(1/(A3-1))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]gear[/TD]
[TD]ideal[/TD]
[TD]step[/TD]
[TD]cog[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=IF(A8<=$A$3,$C$3*$B$5^($A$3-A8),FALSE)[/TD]
[TD][/TD]
[TD]=B8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=A8+1[/TD]
[TD]=IF(A9<=$A$3,$C$3*$B$5^($A$3-A9),FALSE)[/TD]
[TD]=IF(B9,ROUND(B9-B8,0))[/TD]
[TD]=IF(B9,C9+D8)[/TD]
[TD]=IF(B9,D8/D9)[/TD]
[/TR]
[TR]
[TD]=A9+1[/TD]
[TD]=IF(A10<=$A$3,$C$3*$B$5^($A$3-A10),FALSE)[/TD]
[TD]=IF(B10,ROUND(B10-B9,0))[/TD]
[TD]=IF(B10,C10+D9)[/TD]
[TD]=IF(B10,D9/D10)[/TD]
[/TR]
[TR]
[TD]=A10+1[/TD]
[TD]=IF(A11<=$A$3,$C$3*$B$5^($A$3-A11),FALSE)[/TD]
[TD]=IF(B11,ROUND(B11-B10,0))[/TD]
[TD]=IF(B11,C11+D10)[/TD]
[TD]=IF(B11,D10/D11)[/TD]
[/TR]
[TR]
[TD]=A11+1[/TD]
[TD]=IF(A12<=$A$3,$C$3*$B$5^($A$3-A12),FALSE)[/TD]
[TD]=IF(B12,ROUND(B12-B11,0))[/TD]
[TD]=IF(B12,C12+D11)[/TD]
[TD]=IF(B12,D11/D12)[/TD]
[/TR]
[TR]
[TD]=A12+1[/TD]
[TD]=IF(A13<=$A$3,$C$3*$B$5^($A$3-A13),FALSE)[/TD]
[TD]=IF(B13,ROUND(B13-B12,0))[/TD]
[TD]=IF(B13,C13+D12)[/TD]
[TD]=IF(B13,D12/D13)[/TD]
[/TR]
[TR]
[TD]=A13+1[/TD]
[TD]=IF(A14<=$A$3,$C$3*$B$5^($A$3-A14),FALSE)[/TD]
[TD]=IF(B14,ROUND(B14-B13,0))[/TD]
[TD]=IF(B14,C14+D13)[/TD]
[TD]=IF(B14,D13/D14)[/TD]
[/TR]
[TR]
[TD]=A14+1[/TD]
[TD]=IF(A15<=$A$3,$C$3*$B$5^($A$3-A15),FALSE)[/TD]
[TD]=IF(B15,ROUND(B15-B14,0))[/TD]
[TD]=IF(B15,C15+D14)[/TD]
[TD]=IF(B15,D14/D15)[/TD]
[/TR]
[TR]
[TD]=A15+1[/TD]
[TD]=IF(A16<=$A$3,$C$3*$B$5^($A$3-A16),FALSE)[/TD]
[TD]=IF(B16,ROUND(B16-B15,0))[/TD]
[TD]=IF(B16,C16+D15)[/TD]
[TD]=IF(B16,D15/D16)[/TD]
[/TR]
[TR]
[TD]=A16+1[/TD]
[TD]=IF(A17<=$A$3,$C$3*$B$5^($A$3-A17),FALSE)[/TD]
[TD]=IF(B17,ROUND(B17-B16,0))[/TD]
[TD]=IF(B17,C17+D16)[/TD]
[TD]=IF(B17,D16/D17)[/TD]
[/TR]
[TR]
[TD]=A17+1[/TD]
[TD]=IF(A18<=$A$3,$C$3*$B$5^($A$3-A18),FALSE)[/TD]
[TD]=IF(B18,ROUND(B18-B17,0))[/TD]
[TD]=IF(B18,C18+D17)[/TD]
[TD]=IF(B18,D17/D18)[/TD]
[/TR]
[/TABLE]

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.

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.

Well, sweeter in that it follows the curve more closely. I wouldn't try to use this to argue with preference.

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.
[TABLE="class: grid, width: 500"]
[TR]
[TD][/TD]
[TD]Tire size 700c x 32mm
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]rim mm
[/TD]
[TD]622
[/TD]
[TD]630 (27), 622 (700c), 559 (26 & fat)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tire mm
[/TD]
[TD]32
[/TD]
[TD]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)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tire dia
[/TD]
[TD]=(B2+2*B3)/25.4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]top
[/TD]
[TD]bottom
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ring steps
[/TD]
[TD][/TD]
[TD]=LN(F8)/LN(D25)
[/TD]
[TD]=LN(G8)/LN(D25)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ratio of rings
[/TD]
[TD][/TD]
[TD]=E10/F10
[/TD]
[TD]=F10/G10
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2-step ratio
[/TD]
[TD]1-step ratio
[/TD]
[TD]gear
[/TD]
[TD][/TD]
[TD]3rd
[/TD]
[TD]2nd
[/TD]
[TD]1st
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]road triple
[/TD]
[TD]52
[/TD]
[TD]39
[/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8x11 to 30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]11
[/TD]
[TD]no gear
[/TD]
[TD]=IF($D12="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D12*E$10*$B$4))
[/TD]
[TD]=IF($D12="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D12*F$10*$B$4))
[/TD]
[TD]=IF($D12="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D12*G$10*$B$4))
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(D12="no gear","",D14/D12)
[/TD]
[TD]=IF(D12="no gear","",D13/D12)
[/TD]
[TD]10
[/TD]
[TD]no gear
[/TD]
[TD]=IF($D13="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D13*E$10*$B$4))
[/TD]
[TD]=IF($D13="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D13*F$10*$B$4))
[/TD]
[TD]=IF($D13="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D13*G$10*$B$4))
[/TD]
[TD]high
[/TD]
[TD]=MAX(E11:G22)
[/TD]
[/TR]
[TR]
[TD]=IF(D13="no gear","",D15/D13)
[/TD]
[TD]=IF(D13="no gear","",D14/D13)
[/TD]
[TD]9
[/TD]
[TD]no gear
[/TD]
[TD]=IF($D14="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D14*E$10*$B$4))
[/TD]
[TD]=IF($D14="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D14*F$10*$B$4))
[/TD]
[TD]=IF($D14="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D14*G$10*$B$4))
[/TD]
[TD]low
[/TD]
[TD]=MIN(E11:G22)
[/TD]
[/TR]
[TR]
[TD]=IF(D14="no gear","",D16/D14)
[/TD]
[TD]=IF(D14="no gear","",D15/D14)
[/TD]
[TD]8
[/TD]
[TD]11
[/TD]
[TD]=IF($D15="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D15*E$10*$B$4))
[/TD]
[TD]=IF($D15="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D15*F$10*$B$4))
[/TD]
[TD]=IF($D15="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D15*G$10*$B$4))
[/TD]
[TD]overall ratio
[/TD]
[TD]=I13/I14
[/TD]
[/TR]
[TR]
[TD]=IF(D15="no gear","",D17/D15)
[/TD]
[TD]=IF(D15="no gear","",D16/D15)
[/TD]
[TD]7
[/TD]
[TD]13
[/TD]
[TD]=IF($D16="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D16*E$10*$B$4))
[/TD]
[TD]=IF($D16="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D16*F$10*$B$4))
[/TD]
[TD]=IF($D16="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D16*G$10*$B$4))
[/TD]
[TD]chain wrap
[/TD]
[TD]=MAX(E10:G10)-MIN(E10:G10)+MAX(D1222)-MIN(D1222)
[/TD]
[/TR]
[TR]
[TD]=IF(D16="no gear","",D18/D16)
[/TD]
[TD]=IF(D16="no gear","",D17/D16)
[/TD]
[TD]6
[/TD]
[TD]15
[/TD]
[TD]=IF($D17="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D17*E$10*$B$4))
[/TD]
[TD]=IF($D17="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D17*F$10*$B$4))
[/TD]
[TD]=IF($D17="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D17*G$10*$B$4))
[/TD]
[TD]RD max cog
[/TD]
[TD]=MAX(D1222)
[/TD]
[/TR]
[TR]
[TD]=IF(D17="no gear","",D19/D17)
[/TD]
[TD]=IF(D17="no gear","",D18/D17)
[/TD]
[TD]5
[/TD]
[TD]17
[/TD]
[TD]=IF($D18="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D18*E$10*$B$4))
[/TD]
[TD]=IF($D18="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D18*F$10*$B$4))
[/TD]
[TD]=IF($D18="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D18*G$10*$B$4))
[/TD]
[TD]FD diff
[/TD]
[TD]=MAX(E10:G10)-MIN(E10:G10)
[/TD]
[/TR]
[TR]
[TD]=IF(D18="no gear","",D20/D18)
[/TD]
[TD]=IF(D18="no gear","",D19/D18)
[/TD]
[TD]4
[/TD]
[TD]20
[/TD]
[TD]=IF($D19="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D19*E$10*$B$4))
[/TD]
[TD]=IF($D19="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D19*F$10*$B$4))
[/TD]
[TD]=IF($D19="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D19*G$10*$B$4))
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(D19="no gear","",D21/D19)
[/TD]
[TD]=IF(D19="no gear","",D20/D19)
[/TD]
[TD]3
[/TD]
[TD]23
[/TD]
[TD]=IF($D20="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D20*E$10*$B$4))
[/TD]
[TD]=IF($D20="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D20*F$10*$B$4))
[/TD]
[TD]=IF($D20="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D20*G$10*$B$4))
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(D20="no gear","",D22/D20)
[/TD]
[TD]=IF(D20="no gear","",D21/D20)
[/TD]
[TD]2
[/TD]
[TD]26
[/TD]
[TD]=IF($D21="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D21*E$10*$B$4))
[/TD]
[TD]=IF($D21="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D21*F$10*$B$4))
[/TD]
[TD]=IF($D21="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D21*G$10*$B$4))
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]=IF(D21="no gear","",D22/D21)
[/TD]
[TD]1
[/TD]
[TD]30
[/TD]
[TD]=IF($D22="no gear", "no gear",IF(E$10="no gear", "no gear",1/$D22*E$10*$B$4))
[/TD]
[TD]=IF($D22="no gear", "no gear",IF(F$10="no gear", "no gear",1/$D22*F$10*$B$4))
[/TD]
[TD]=IF($D22="no gear", "no gear",IF(G$10="no gear", "no gear",1/$D22*G$10*$B$4))
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]cog ratio
[/TD]
[TD]=D22/MIN(D1221)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]# gears
[/TD]
[TD]=COUNT(B13:B22)+1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]one step
[/TD]
[TD]=(MAX(D1222)/MIN(D1222))^(1/(D24-1))
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]half step
[/TD]
[TD]=SQRT(D25)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1.5 step
[/TD]
[TD]=D26^3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2 step
[/TD]
[TD]=D25^2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3 step
[/TD]
[TD]=D27^2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

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. )

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.

My "rule" is trying to get about 10% jumps.

@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.

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.

It's all Greek to me.