Spreadsheet to calculate gearbox ratios and shift points

Comments are welcome !

Edit : version 5.01 (English or French) with max vehicle speed available.





Here is the fifth version of a spreadsheet i made to automatically calculate and visualize the spacing of gearbox ratios based on:

- the desired gear for 1st gear
- the desired gear for the last gear
- the number of gears
- the logarithmic coefficient k for logarithmic spacing

It is possible to calculate gearboxes up to 20 speeds.

Then i did add the math and formulas to calculate the theoretical shift points to maximize the vehicle's acceleration. There is some VBA code for computations, so you'll need to enable modification when opening the document. If not you will loose the shift points function.

It is not so easy to do in a spreadsheet. But can be done with some (quite hard) work.

The graphs on the "Other graphs" page show the curves for a linear spacing and for a logarithmic spacing of coefficient k.

This spacing is not fully optimized, but it can be used as a basis. The gear ratio values can be manually changed to adjust the spacing (do not save the spreadsheet after these changes, to avoid losing the formulas).

In practice, following a linear or logarithmic progression is not necessarily the best solution. Modifications to certain ratios sometimes make sense to improve performance depending on the characteristics of the vehicle and its use. For example, on a vehicle intended for light off-road use that does not have a transfer case, the first gear will often be overshifted to facilitate crossing.

For transmissions in common vehicles (such as compact cars or sedans), the gap between first and second gear is often larger, while the gaps between other gears are closer. A rough estimate based on the analysis of real gearbox steppings leads to a k-coefficient between 0.05 and 0.15 in most vehicles. For off-road vehicles, for engines with atypical torque characteristics and curves, or gearboxes with a reduced number of gears, it can be significantly higher, values from 0.3 to 0.6 are possible.

The two graphs on the first page are the most important ones.

The first one represent the maximum torque available on the wheels for each gear as a function of the vehicle's speed, and therefore the available traction.

The maximum acceleration will theoretically be obtained when the traction and therefore the torque to the wheels is maximum in all gears.

This condition is achieved when each shifting is made at the crossing of the curves, and when the choice of gears maximizes the area under the curves (simplifying because in reality other parameters including the time required to change the gears can affect this theoretical scheme).

The second one shows the gear shift points with the associated engine speeds as well as the speeds in km/h.

The shifts points are positioned on the intersections of the curves of the 1st graph.

NB: Optimizing shift points is a complex subject, which depends on many parameters, some being related to the vehicle and the type of gearbox, others to its use and the track itself. This spreadsheet is intended to give a basis, it is not a tool to establish a complete optimization, for which the vehicle would have to be simulated in its entirety and supplemented by field checks.

An example based on average values for a 5-speed gearbox:

1st Gear : 3.7:1
2nd Gear : Approx. 2.1:1
3rd Gear : Approx. 1.5:1
4th Gear : About 1.2:1
5th Gear : 1:1 (Direct Drive)

These values suggest that the reduction in the difference between the speeds is moderate, which corresponds to a coefficient k of the order of 0.1 in this example.
Although there is no fixed value for k in automotive transmissions, an estimate for conventional vehicles is between 0.05 and 0.15. This corresponds to a gradual reduction in the differences between the gears after the first gear, while ensuring a smooth increase in engine speed. The coefficient may vary depending on the specific performance and fuel economy needs of different types of vehicles.

 

Here is a new version of my gearbox ratio and shift points calculator with a language selector at the top of the first page and a new graphic with effective power by gear.

Could be extended with other languages.

https://www.stonerecording.art/2024...-les-points-de-changement-jusqua-20-rapports/