Do you particularly need a stand alone spreadsheet?

There are a few more modern alternatives.

Ride the Gear Train software (https://ridethegeartrain.com/) does all sorts of things.

Evolvent Design has some good stuff (https://evolventdesign.com/pages/calculators). Change gears are near the bottom on the left.

This one is also good (http://ninoslavp.users.sbb.rs/WRI_en.html) You can look up where the .rs domain is located (Serbia).

This, from Evolvent, is interesting side reading on the maths involved (https://www.patreon.com/posts/solving-gear-for-87487774). As well as the Pfauter book mentioned, there is a Barber Colemn one and another called 14000 gear ratios by Ray Page.

Another survey of the maths: https://www.geartechnology.com/ext/resources/issues/0884x/orthwein.pdf

Another change gear program here: https://conradhoffman.com/chsw.htm

More: https://www.globalspec.com/reference/68675/203279/part-1-use-of-tables-for-simplified-change-gear-calculations

Brocot tables here: https://locomat.loria.fr/merritt1947/merritt1947doc2.pdf

I have just found a reference which may be useful:

https://www.polytechforum.com/modelengineering/gear-hobbing-54531-.htm

Sorry, I can’t be bothered with that site’s cookie-rejection process, so I haven’t delved any deeper.

MichaelG.

That’s why I posted the “maybe useful”  link, Adam

… Was it no use ?

MichaelG.

.

On 18 August 2024 at 13:52 Adam Harris Said:

…one can enter all the change gears one has and the desired ratio output, then it spits out the gear train possibilities with the lowest error.

Spreadsheets are not good at doing what you describe (because they do not do well at iterating through lists). You have a list with ‘n’ items in it. Let us say you want only a simple gear train of A/B. How many possibilities are there for it to do an exhaustive search? Pick ‘n’=3 and try to write a spreadsheet that does this calculation. See how you would have to modify the spreadsheet to cope with changing ‘n’ to 4. Now see how you would have to modify the spreadsheet to make an A/B x C/D geartrain.

The mathematical gymnastics required for the task are much more suited to a general purpose programming language (Basic, C, Python, etc.) than the limited capabilities of a spreadsheet. These programs can be hidden behind a webpage where you only write input and look at output. That is why the majority of the programs you see are stand-alone bespoke applications and not based on a spreadsheet.

No doubt there is a spreadsheet Jedi who could make Excel do this work, but it is on the outer bounds of the design parameters of the program.

There is a spreadsheet here:

Workshop Calculations

It is free but requires you to send the author your email in order to receive the download link.

Another couple to add to your list:

NthreadsP here: https://www.lathes.co.uk/latheparts/page14.html

Look in Marv Klotz’ utilities as there is a program there. His website is offline at the moment but someone has mirrored the originals on GitHub and also translated a selection of them from C to Python.

Any standalone program that is not web-based would allow you to use a data file of the gears you have so you only ever enter the information once.

Please re-read the patreon post by Evolvent Design. It explains the maths behind the calculations (note how it explains that the Pfauter book, good though it is, is not exhaustive). Modern computers are so fast and so cheap that you now can do an exhaustive search of every possiblity with a few lines of programming but a spreadsheet is not the place to do this.

Adam, read to the bottom of Michael’s link, last post was 1 year ago and he was still offering to share the file.

On 18 August 2024 at 17:24 Adam Harris Said:

Many thanks but I don’t fancy downloading from that glue-it.com which seems to specialize in free software presumably in order to get downloaders onto spam and ad lists.

Your choice. The author is here:

https://www.ukworkshop.co.uk/threads/thread-cutting-change-gears.134512/

and may not take kindly to your allegations about spam and ad lists. If you have some evidence to back up this statement, please present it.

You mentioned John F’s Workshop. Have you found the page on his site mentioning this spreadsheet? He offers to send it to enquirers if you contact him. There is a post dated 02/02/24 by him on the site’s home page that gives his email address.

Hobnail – software for computing gear train

Please also see this one, which does what the spreadsheet does and saves the gear list as you desire:

https://metal.duncanamps.com/software.php

On 18 August 2024 at 22:02 Adam Harris Said:

Duncanamps does not store change gear stock data once programme closed …

Oh yes it does.

I am 99% sure this will not end well, because the language uses indentation as part of its syntax and this does not seem possible in a forum post. However, below is some Python code that might work out a few gear trains. Hopefully it is clear where, near the top of the program, the data is entered.

With the data below, it takes about 10 seconds on my computer to complete the a/b * c/d * e/f calculation.

—

from math import pi as pi # just in case someone needs module or DP
import sys # so program can exit gracefully if not enough gears listed
import itertools as it # permutions and combinations

“”” Fill list with gear sizes, separated by comma, order unimportant “””

gears = [2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61,
67, 71, 73, 79, 83, 89, 97]

required_gearing = pi/3 # enter gear ratio as fraction or decimal. pi/3 is OK

compounding_number = 2 # 1 = A/B; 2 = A/B * C/D; 3 = A/B * C/D * E/F

if len(gears) < 2 * compounding_number: # check we have enough gears
print (“Not enough gears to form requested compound chain”)
sys.exit()

layout = [“A/B”, “A/B * C/D”, “A/B * C/D * E/F”]

print (“Contents of gears”) # check gears correctly entered
print (gears, “\n”)
print (“Gear layout”, layout[compounding_number-1], “\n”)

“”” Main calculations start below “””

ratio=[] # empty list to store calculated ratio

def ab_train(): # A/B
for i in it.combinations(gears, 2): # pick two gears from list
r = i[0]/i[1] # calculate their ratio
y = [abs(r – required_gearing), (i), r] # abs item is error
ratio.append(y) # add to list
return ratio

def abcd_train(): # A/B * C/D
for i in it.combinations(gears, 4): # pick four gears from list
for j in it.combinations(i,2 ):
# pick two gears from the four already selected
k = tuple(set(i).difference(j)) # store the other two
r = (j[0]*j[1])/(k[0]*k[1]) # calculate ratio
y = [abs(r – required_gearing), (j, k), r] # abs item is error
ratio.append(y) # add to list
return ratio

def abcdef_train(): # A/B * C/D * E/F
for i in it.combinations(gears, 6): # pick six gears from list
for j in it.combinations(i, 3):
# pick three gears from the six already selected
k = tuple(set(i).difference(j)) # store the other three
r = (j[0]*j[1]*j[2])/(k[0]*k[1]*k[2]) # calculate ratio
y = [abs(r – required_gearing), (j, k), r] # abs item is error
ratio.append(y) # add to list
return ratio

“”” sometimes an A/B might give closer results than a compound train “””
“”” thus we must test all lower options even when a compound is requested “””
“”” example: gears = [2, 3, 5, 7, 9, 11] required_gearing = 7/9 “””
“”” no compound train will do better than the exact 7/9 “””

match compounding_number:
case 1: # A/B
ab_train()
case 2: # A/B * C/D
ab_train()
abcd_train()
case 3: # A/B * C/D * E/F
ab_train()
abcd_train()
abcdef_train()

best = min(ratio) # nearest combination is the one with minimum error
print (“Nearest to:”, required_gearing, best[1])
print (” Actual:”, best[2])
print (” Error:”, best[0])