Tool to calculate and optimize bike gearing components

[scrambler]

Hi, I am fairly new here, but I have been a regular reader of the forum for a while now, learning from the bests to educate myself on eBikes. I do plan to build or acquire/customize a high performance eBike in the near future.

THE TOOL:
Being interested in getting a productive pedal assist mode up to the max legal speed of 28mph, I needed to find out the pedaling cadence at every speed of existing bikes, as well as how to configure any customization for optimal pedal assist on a wide range of speeds (and optimal motor rpm on mid drives)

So I decided to build a simulator in Excel (file download below) to help with that.
To be sure to always get the latest version use the download location below.
https://onedrive.live.com/redir?resid=4BE668230430EFDD!1111&authkey=!AOdx1YqUZkbO44M&ithint=folder%2cxlsx

In case there is a problem with the download location, there is a local copy at the bottom of the post (I will try to keep it up to date...)

The Excel file contains 3 sheets.

Rear Hubs:
lets you specify a number of variables for a rear hub motor bike, like rim & tire size, Rear gears, Bottom Bracket gears, sprocket and chainring teeth numbers….
It computes in real time the rpm at every level of the transmission; wheel, sprocket, chainring and crank (pedaling cadence), and that for 9 different speeds (as well as any custom speed you want). You can also select any gear you want (front and back), for any of the speeds, to see the influence on the pedaling cadence.
Playing with the variables, you can visualize the pedaling cadence at any speed for any given configuration, and optimize sprocket and chainring values.

Mid Drives:
Similar to the above, but adds the transmission elements between the motor and the chainring, including the motor internal reduction ratio.
As a result, in addition to visualizing and optimizing the pedaling cadence, you can also visualize and optimize the rpm range of the mid drive motor.

References:
Includes some articles and reference sites I found useful during my research.

I would be happy to add more configurations to it if any one needs.

I don’t know if it can help anyone else but I thought it could not hurt to share. If it has already been done, I apologize for wasting your time

All this is still fairly new to me, so feel free to point out any flaws with the concept, or issues with the tool.

Thank you to the community for being such a great source of information!

EDIT (sept 14th): Added a second mid drive configuration.
The two mid drive configurations now are
Mid Drive configuration ONE: One chain between motor and crank, one chain between crank and rear wheel
You can set the following: Rear sprocket/gears; Chainring linked to the rear; Chainring linked to the motor; Motor sprocket and internal gear ratio

Mid drive configuration TWO: One chain between crank BB and motor, one chain between motor and rear wheel
You can set the following: Rear Sprocket; Motor Sprocket linked to rear; Motor sprocket linked to BB; BB Chainring; BB gears; Motor internal gear ratio

 

[amberwolf]

Unfortunately the site just comes up with a blank page for me.

Can you attach the file directly to a post in this thread?

There should be a Browse button below the text box when you are typing a reply. Browse to the file on your local computer, then choose "add the file" once you have done so and closed the browse dialog box.

If it will not add your file because of file type, you can create a .zip file of it instead, or even just rename it's file extension to .zip, if necessary.

 

[scrambler]

Unfortunately the site just comes up with a blank page for me.

Can you attach the file directly to a post in this thread?

I was updating the file which may have caused your problem. It works for me now, so can you try again and let me know.
Ideally the link would be best, as I can easily update the file as needed without having to edit the post.

EDIT: I also added a local copy to cover all the bases

 

[amberwolf]

Thanks--the attached file downloads fine. (have to get it over to OpenOffice and test it now).

The onedrive site itself displays blank even on it's homepage. It's probably trying to do something my browsers don't understand (they're older versions because the newer ones are not even able to install on my ancient computer).

 

[RageNR]

I am honestly not trying to poke fun at you, as you seemed to put a lot of effort into this.
But I have to ask, how is this different than other tools available? The one in particular that comes to mind is GearCalculator on the Play Store: https://play.google.com/store/apps/details?id=com.gearcalculator&hl=en

I do not have excel, so I can not open your document to look at it. Just curious

 

[scrambler]

I have not used the tools you referred to so I cant tell you if the one I made is better or worse, or just different.

You say you have not looked at mine so I guess you do not know either

As I mentioned in my post, it is very possible that there are similar tools already available, and if so, so be it.

But I did do these to help me grasp all the details of the transmission, and excel is easy so it was no effort at all, so I thought it cannot hurt to share.

If it is of no use to you or others, no big deal

 

[scrambler]

I updated the Excel sheet in the first post to include a second mid drive configuration.
The configuration supported now are:

Rear Hub Motor
You can set the following: Single or multiple gears at the rear; Single or multiple gears at the Bottom Bracket

Mid Drive configuration ONE: One chain between motor and crank, one chain between crank and rear wheel
You can set the following: Rear sprocket/gears; Chainring linked to the rear; Chainring linked to the motor; Motor sprocket and internal gear ratio

Mid drive configuration TWO: One chain between crank BB and motor, one chain between motor and rear wheel
You can set the following: Rear Sprocket; Motor Sprocket linked to rear; Motor sprocket linked to BB; BB chainring; BB gears; Motor internal gear ratio