Excel Gear Inches Calculator - Updated Version

For discussions about bikes and equipment.
Manc33
Posts: 1388
Joined: 25 Apr 2015, 9:37pm

Excel Gear Inches Calculator - Updated Version

Postby Manc33 » 8 Sep 2019, 3:43am

On this chart, you get shown figures with three charts at once, covering:

- Gear Inches
- Distance Traveled
- Wheel Turns


With this latest version, you can:

- Enter your wheel circumference in millimeters and directly see what the wheel diameter is, in inches. This is handy if you have already got the circumference written down somewhere or entered into a bike computer, because doing it this way means no measuring at all needs to be done, just put the millimeters value into the calc and it tells you the wheel diameter in inches, that can then be entered into the main "Wheel Diameter" box.

- Enter the circumference in any units to get the diameter. This doesn't relate to millimeters or inches and is simply a direct calculation of circumference value to diameter value.

- See what your inches diameter works out to in feet (for this, you need to have entered it in inches in the main Wheel Diameter box).

- See what the circumference is. This doesn't relate to millimeters or inches and is simply a direct calculation of whatever wheel diameter you entered in the main box, to a circumference value.

- See what the inches diameter works out to in meters. This only works if you have entered inches for the Wheel Diameter and it's here so you can then type this figure into the diameter box to see "Meters Development" in the middle chart.

- See the circumference in millimeters, assuming you entered your Wheel Diameter in inches in the main box. It's not likely anyone would measure the diameter first (hub gets in the way of taking an accurate measurement!) but this is also included here anyway.


Other info:

If you want your units in meters, it's easy - you can just put your wheel diameter in meters and that's it done. This would then translate to "Meters Development" in the middle chart where distance traveled is displayed.

With the colour coding this also has, you can quickly see where gear combinations are overlapping.

Inspired by the Sheldon Brown calculator, this gear calculator covers up to 12-speed and also includes the difference between each sprocket, for any given chainring and sprocket combination, in the grey boxes between the main coloured boxes.

Here's the full blurb from the info in the spreadsheet:

Code: Select all

 These charts can show you the gear inches (or feet, or meters) your
 chainring and sprocket combinations are going to end up giving you.

 It can cover a single, double or triple chainset, up to 12 sprockets.

 Firstly, type your wheel diameter in the box under "Wheel Diameter".
 This wheel diameter can be any unit of measurement you want, E.G.
 inches, meters, feet, etc. Whatever units you choose for the wheel
 diameter will also be the units that come out on the charts.

 YOU ONLY NEED TO ENTER YOUR CHAINRING AND SPROCKET TEETH ON THE FIRST
 CHART, THE OTHER TWO CHARTS WILL AUTOMATICALLY UPDATE THEMSELVES.

 In the blue box to the right of the "LOW" box, type in the number of teeth on
 your cassettes low (biggest) sprocket and carry on filling in the blue boxes going
 down that column, until you end up at your high (smallest) sprocket.

 Do not delete anything from any of the boxes. If you are not running a 12-speed
 cassette and don't need to use the spare blue boxes for sprockets you do not have,
 just put a 0 in those boxes, all the figures will still be accurate for the sprockets
 you do enter. All the numbers you type in the sprocket (and chainring) blue
 boxes have a "t" after the number, meaning "teeth".

 The purple boxes between sprockets and between chainrings show you the % gear
 jump between each sprocket and chainring. You should start with the low (biggest)
 sprocket and work your way to the high (smallest) sprocket because then, the
 percentage jump between each sprocket will be correct.

 Type in the chainring teeth in the blue boxes under "Chain Rings". This should have
 the smallest chainring first, again so the percentage jump in between is displayed
 accurately. The results in the grey boxes are the difference between each gear jump.

 When you have filled in your wheel diameter, chainring teeth and cassette teeth, you
 will end up with the lowest gear at the top left and highest gear at the bottom right.
 The figures are coloured, with the lowest gear in green, middle gears in yellow and
 high gears in red.



DOWNLOAD:

Code: Select all

https://www.sendspace.com/file/ra3pkt



The spreadsheet is protected so you can only edit the cells you need to edit, this stops any accidental deleting of anything, but if you want to unprotect it, the password is "password".

Screenshot of the chart...

Image
Last edited by Manc33 on 11 Sep 2019, 3:44am, edited 10 times in total.
Only weird bikes are interesting anymore.

NetworkMan
Posts: 645
Joined: 25 Aug 2014, 11:13am
Location: South Devon

Re: Excel Gear Inches Calculator - Updated Version

Postby NetworkMan » 8 Sep 2019, 10:05am

Very swish! Mine is monochrome and far more utilitarian looking. I'll stick with mine since it has the table laid out horizontally and I can put alternative designs in one under the other. Everyone to his own ....
BTW I recommend Libreoffice - open source, free of charge and you are not beholden to Microsoft.

Brucey
Posts: 35228
Joined: 4 Jan 2012, 6:25pm

Re: Excel Gear Inches Calculator - Updated Version

Postby Brucey » 8 Sep 2019, 12:09pm

why would you want the measurement in feet....? Are there not enough (different) ways of expressing the gear already....? :shock: :shock:

cheers
-------------------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~Brucey~~~~~~~~~~~~~~~~~~~~~~~~

mercalia
Posts: 11107
Joined: 22 Sep 2013, 10:03pm
Location: london South

Re: Excel Gear Inches Calculator - Updated Version

Postby mercalia » 8 Sep 2019, 4:57pm

I have an app on my Windows phone, was free

Manc33
Posts: 1388
Joined: 25 Apr 2015, 9:37pm

Re: Excel Gear Inches Calculator - Updated Version

Postby Manc33 » 8 Sep 2019, 5:33pm

OpenOffice (free) can open both .xls and .xlsx files. Whether that comes out looking like it does in Excel, I have no idea.

Every number in every coloured cell needs multiplying by Pi to get the distance traveled. :?

I have never understood that part. If you put in a 36t chainring and a 36t cassette sprocket on 26" wheels, the distance traveled would be 26" x 3.14159 = 81.68" (not 26" as per "gear inches"). So gear inches seems like some arbitrary thing that doesn't mean anything. :|

It seems like one of those things where everyone does it that way but no one asks why. Because of this, the numbers in the grey cells aren't really telling you the distance traveled between gears, nor are the gears coming out telling you the real distance traveled. This was always my original gripe with it last time around making that crude version of this same thing.

I might change it because knowing the distance traveled is surely the whole point. :roll:

My gear range on this chart came out at 15.4" to 110.2" :mrgreen: (Or in distance traveled: 4.2ft to 29.7ft).

Do measure your wheel though because my so-called 26" wheel isn't 26", it's actually more like 25.25". It all depends what tyres you have got on and then, how pumped up they are, too.

I worked out on my 700 x 25c wheels, my top gear of 48x12 gives about 106.5" while a 26" x 1.35" setup would give 108.1" on 48x11. So if you swapped from 700c slicks to slightly fatter slicks on a 26" you're going to need to have a high sprocket 1t smaller to even it out again and have roughly the same highest gear. At the low end things are affected more and 22x36 on 700c comes out to 16.3 gear inches while that same low gear on 26" wheels comes out to 15.1 gear inches. That extra 1.2" doesn't sound like a lot but it's about 40% of a gear at such low gearing, which would be comparable to something like a 32t low sprocket becoming a 34t just because 700c were swapped for 26" - every little helps, we're not getting any younger, knees and all that. :) :roll:
Only weird bikes are interesting anymore.

NetworkMan
Posts: 645
Joined: 25 Aug 2014, 11:13am
Location: South Devon

Re: Excel Gear Inches Calculator - Updated Version

Postby NetworkMan » 8 Sep 2019, 7:29pm

Hello Manc33,
As far as I can see both your files look just like your example when loaded into LibreOffice Calc. which is always the case IIRC.

Gear inches give you the equivalent diameter of the wheel on an ordinary bicycle. If I was about to buy a new-fangled safety bicycle, around the turn of the century perhaps, then that information might actually be nice to know.

Nowadays it probably doesn't matter what units are used; all we need is a length so that we can compare one bicycle or gearing arrangement with another. What does matter is that the more measurements of length there are, the more confusion there will be. Personally I don't much care how far my bike goes each time I turn the cranks, and in any case that's what "development" does which is usually given in metres.

You said that the pressure in a tyre affects the rolling radius but that effect will be pretty miniscule since the tyre is pretty inelastic and even a 2-3 percent error will be pretty inconsequential. I'm sure that using ISO wheel size+2*ISO tyre size will be just fine for gear calculations and having to multiply the result by pi is just extra bother!

Brucey
Posts: 35228
Joined: 4 Jan 2012, 6:25pm

Re: Excel Gear Inches Calculator - Updated Version

Postby Brucey » 8 Sep 2019, 9:58pm

^ I agree.


Development is just another arbitrary number, and you might as well express it in metres since at least you can have a meaningful conversation with Europeans about gearing, instead of, er, no-one .

All you need to remember is that if you pedal a 100" gear at 100rpm, that is pretty close to 30mph. Everything else scales to that.

I quite like this gearing calculator


http://ritzelrechner.de/?GR=DERS&KB=48,52&RZ=13,14,15,16,17,18,19&UF=2150&TF=100&SL=1.7&UN=MPH&DV=gearInches&GR2=DERS&KB2=42,52&RZ2=13,14,15,16,17,19,21&UF2=2150

as configured above it shows some TT gearing I used to use, compared with training/hilly gearing, in mph /gear inches. You can set up the calculator to give results in a variety of ways.

cheers
-------------------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~Brucey~~~~~~~~~~~~~~~~~~~~~~~~

Manc33
Posts: 1388
Joined: 25 Apr 2015, 9:37pm

Re: Excel Gear Inches Calculator - Updated Version

Postby Manc33 » 9 Sep 2019, 1:24am

I don't want to talk to Europeans. Just kidding, Europeans.

I just looked it up and Google says only three countries in the world still use feet and inches: United States, Myanmar and Liberia. :lol:

Thing is we use both here, we're clever and swap between either. :mrgreen:
Only weird bikes are interesting anymore.

Manc33
Posts: 1388
Joined: 25 Apr 2015, 9:37pm

Re: Excel Gear Inches Calculator - Updated Version

Postby Manc33 » 9 Sep 2019, 2:35pm

I just updated it - and made it miles better.

Grab the new 3-in-1 chart from the link in the first post.

It's probably not going to have (or need) any more updates after this. :D

EDIT: Despite saying that I have done a few minor updates to it:

- Made it so teeth can't be edited on chart #2 and chart #3. No need to edit those (it automatically copies from chart #1)
- Got rid of white cells making the file bigger (cleared those, removed grid, same end result)
- Put in the proper sprocket numbers for 12-Speed 10-51t cassette.
- Default wheel diameter changed to 700 x28c, no real reason.
- Changed chainring numbers to 24-38-52 (my favourite ever triple), no real reason.


It's a shame there's no rear mech with a swingarm big enough to take the huge chain such a setup would require, if there was I'd be running something close to it! Maybe not a 12-Speed but something like the M8000 one (11-Speed) at 11-46t. You'd be snapping the chain or bending spokes (or both) in the lowest gear @ 24x46t :lol:
Only weird bikes are interesting anymore.

Manc33
Posts: 1388
Joined: 25 Apr 2015, 9:37pm

Re: Excel Gear Inches Calculator - Updated Version

Postby Manc33 » 11 Sep 2019, 3:50am

It's been updated again, mainly to add more features down the left side. :twisted:
Only weird bikes are interesting anymore.

NickJP
Posts: 116
Joined: 24 Sep 2018, 7:11pm

Re: Excel Gear Inches Calculator - Updated Version

Postby NickJP » 11 Sep 2019, 7:32am

I made a gearing spreadsheet for my own use a number of years ago. You can compare gear inches, development, and kph at a nominated cadence for two different gear setups. In ODS (Libreoffice) format - I believe modern versions of Excel can open this also:

https://www.dropbox.com/s/3vf2umc7p1cacjv/gears.ods?dl=0

Manc33
Posts: 1388
Joined: 25 Apr 2015, 9:37pm

Re: Excel Gear Inches Calculator - Updated Version

Postby Manc33 » 11 Sep 2019, 3:06pm

I'm going to have a go at a spoke length calculator next.

Then if that works I will probably throw all three into one unified file covering gear inches, torque calc and spoke length calc. I'm no good at trigonometry though if that's what a spoke length calc involves, forget it. :lol:
Only weird bikes are interesting anymore.

User avatar
The utility cyclist
Posts: 2364
Joined: 22 Aug 2016, 12:28pm
Location: The first garden city

Re: Excel Gear Inches Calculator - Updated Version

Postby The utility cyclist » 11 Sep 2019, 6:41pm

Manc33 wrote:I'm going to have a go at a spoke length calculator next.

Then if that works I will probably throw all three into one unified file covering gear inches, torque calc and spoke length calc. I'm no good at trigonometry though if that's what a spoke length calc involves, forget it. :lol:

There is one already available, spokecalc.xls https://spokecalc.io/about.html

As for working out gear inches etc, I use gear calc app on my phone

NetworkMan
Posts: 645
Joined: 25 Aug 2014, 11:13am
Location: South Devon

Re: Excel Gear Inches Calculator - Updated Version

Postby NetworkMan » 12 Sep 2019, 2:18pm

NickJP wrote:I made a gearing spreadsheet for my own use a number of years ago. You can compare gear inches, development, and kph at a nominated cadence for two different gear setups. In ODS (Libreoffice) format - I believe modern versions of Excel can open this also:

https://www.dropbox.com/s/3vf2umc7p1cacjv/gears.ods?dl=0

That's rather like mine. Google Seets Android can open it, though it saves it on Google drive without asking in perhaps it's own format.

Manc33
Posts: 1388
Joined: 25 Apr 2015, 9:37pm

Re: Excel Gear Inches Calculator - Updated Version

Postby Manc33 » 14 Sep 2019, 3:09am

A spoke length calc is beyond me (for example: =SQRT(E3^2+F3^2+G3^2)-B$7/2) :lol: but I have got the Excel file "SpocalcExpress" that is perfect for my usage.

If the formulas in that can be copied and pasted with it all working and a chain length calc can be added, it could all be thrown into one single spreadsheet with:

Gear Inches
Torque Calc
Spoke Length
Chain Length
Only weird bikes are interesting anymore.