Deriving "gear" from rpm and mph data?

Hey,

In short, this has been making my head hurt for a while.... I'll try and keep it brief:

1. The data logger is sampling @ 20Hz
2. RPM & MPH (among many other things that need not concern us here) are recorded.
3. I know each gear ratio, the "diff" ratio & the tire size.
4. The data file (obviously a spreadsheet) has columns for "rpm", "mph" and an empty "gear" column.

*IF* I could write the appropriate macro I *know* I could populate that "gear" column.

Any macro writers out there?.....

Thanks,
Ian

 

Hi,

Many thanks for the response. However, it *really* made my head hurt...... I see where you're coming from, but:

1. Shouldn't "W" be wheel circumference, not diameter? [And hence where I would expect to see pi "involved".]
2. I see how you arrived at "0.002975" but don't get why pi is involved in deriving this "factor".

Sorry for being dumb!....

Incidentally, I've been (trying!) to think some more on this and discovered Google will convert pretty much anything to anything else directly in the search bar - ie, simply entering "40mph in ft/sec" returns "48.667" or, probably more usefully, "1mph in ft/sec" is 1.4667. [Again, apologies if everyone already knew that, I thought it was pretty cool!]

Anyway, FWIW, my thinking: Given the circumference of the driven wheels and speed we can derive "rear axle rpm".

We also know the diff ratio (4.444) and can hence derive "propshaft" rpm. [ rear axle rpm * 4.444]

The gear ratios:
1 - 3.286
2 - 2.158
3 - 1.609
4 - 1.269
5 - 1.034
6 - 0.848 [BTW, I didn't know 6th was an "overdrive" gear either!]

So, Gear selected ("x") is engine rpm / x = propshaft rpm. Algebra again gives us:

engine rpm / propshaft rpm = x

Where have I gone wrong here?..... Now, writing the damn macro is another tale..... So, smg, yes, please pm (or post if you like) your macros that do it the other way round - May be useful, thanks!

Cheers,
Ian
PS - Off to find some more aspirin......

 

So does my logger (Traqmate) - The bad news is they "forgot" to export "gear selected" - It's there in the analysis, but not in the "csv" export file that I need to merge with video.....

Research continues...... Thanks again to all for the helpful comments,

Cheers,
Ian

 

I eventually got back to this and "blindly" plugged the above into the spreadsheet.

Disaster! - To say the numbers are "all over the map" would be an understatement - Before I even start moving I'm anywhere between gear 45 and gear 123 (!)

I *thought* (and, please remember I'm not an "excel" programmer) it may be the order in which it's doing the calcs thats confusing it (OK, me!) but I've now tried all kinds of combinations and it keeps coming out the same

eg - The following is a few samples after I've started to move - Specifically, speed=5.08 and RPM=3665 and obviously gear 1

G = (0.002975 x 3665) x 24.5) / (5.08 x 4.444) = 11.83 (ish)......

I've tried putting the (S x diff ratio) into another column and plugging that # into the above, no change - The formula looks like this right now:

=0.002975*R47*24.5 / AC47

R47=RPM
AC47 = Speed (mph) x diff ratio (4.444)

I've tried moving the brackets around, but it makes no difference - Which I believe makes sense as it's only multiplying on the left (the "magic number" x RPM X wheel dia (inches))

Help! - Where am I screwing up?....
Thanks in advance,
Cheers,
Ian
PS - I just tried it with 0.002975 x 24.5 = "0.07288" plugged in - Same result (as expected!)

 

Bummer.....

I also had a buddy go down the "tirerack gear calculator" route, and that reached a dead end. However, in classic Topgear fashion, I kept asking myself, "How hard can it be?".....

I believe I've "got it" (logic below if anyone's interested), but am struggling with the macro. In short:

- Given columns containing "propshaft rpm" and "engine rpm" plus a "lookup table" of the ratios, what damn gear am I in?.....

The logic: [#'s are close but approximated for ease of use btw] I've chosen 60mph as it's almost possible to be in any one of four.....

So, speed = 60 mph ~ 1000 inches / sec
Wheel circumference = 80 in.
Therefore, "rear axle RPM" = (1000/80) x60 (seconds) = 750 RPM
Diff ratio = 4:1 (Close enough for now)
Therefore, "Prop shaft" RPM = 750 x 4 = 3000

Lets say the gear ratios are:

1st: 3.2
2nd: 2.2
3rd: 1.6
4th: 1.3
5th: 1.0
6th: 0.8

The logger tells me that as of this sample I'm at 6400 rpm - Guess what? - 3000 x 2.2 = 6400 ie, 2nd is the only possibility!
Conversely, logger rpm = 4800 -> I've got to be in 3rd (etc)

Drum roll!.....

My problem now however is it's not "exact", and I'm struggling with writing the macro.....

Any help *much* appreciated,
TIA,
Ian

 

Huh? Sorry Mike, but did you mean to post that in here? [It does BTW....]

Now, if you want to post your wheel dia, diff ratio and gear ratios, I'll tell you what gear you were in

Cheers,
Ian