I’m far from being an excel guru, but over the years I have turned to it on several occasions and crafted from scratch spreadsheets that do some quite complex calculations and workings and analyses.
An excel guru would probably look at the stuff I done and combined three cells iterative calculations into one much simpler one with a tweak to a function or macro that I am blissfully unaware of.
Nevertheless, the point stands, any old computer running any old spreadsheet gives anyone and everyone access to computative tools that were simply unimaginable 50 years ago.
Now consider a motorcycle final drive chain, one endless roller chain and two sprockets, one driven and one driving.
For the sake of this post we will say the chain is 158 links long, the driving motor sprocket is 17 teeth, and the driven rear wheel sprocket is 48 teeth.
The gear ratio is simplicity itself, 17:48
The GCD function in excel is great for generating and simplifying ratios, as there is (AFAIK) no specific ratio function in excel, but in this case it doesn’t require anything more than simple math to cut it down, 48/17 = a 2.823529411764706 to 1 reduction, so 17:48 is actually shorter to write.
Now we come to an interesting problem, to promote long chain life each tooth on each sprocket should interact with as many links on the chain as possible, as equally as possible.
A 100 link chain and a 25 tooth sprocket means that tooth #1 on the sprocket is only ever going to meet links #1, #26, #51 and #76 and that isn’t good for even wear.
The short rule of thumb has always been divide the number of links in the chain by the number of teeth on the sprocket and look for a long non recurring randomish string of numbers after the decimal point, 3.55445268784664 good, 3.5 bad, 3.6 bad.
Actually doing that calculation for every single tooth on the sprockets and every single link on the chain for say 1 million sprocket revolutions to come up with a complex 3d surface, not so simple, yet this complex 3d surface is exactly the “result” you are seeking, and of course it should be as close to flat and regular as possible, with few if any steep slopes or high peaks.
Disclaimer, I have never sat down and created such a spreadsheet, nor am I aware that anyone ever has, doesn’t stop me from describing its function and utility.
At it’s simplest with our 158 link chain and 48 tooth final drive sprocket then tooth #1 on the sprocket will meet link #1 on the chain, then link #49, then link # 97, then link #145, then the chain is on it’s second pass and the next link is link #35 and so on, just repeat for a million revolutions of the sprocket, then do the same for a sprocket of 47 teeth, and 46 teeth (and 49 teeth and so on) and before you know it you have the data to generate your 3d surface.
It is just the sort of calculation that computers and spreadsheets should be ideal at, boring repetitive number crunching, and it is all solid integer math too, so no problems there.
Actually, it’s a lot harder to do than it seems.
In my own personal case it means sitting down and struggling with excel, though I am sure an excel guru could knock something up in 30 minutes, but that isn’t necessarily the hardship I refer to.
The hardship I refer to is the fact that the motorcycle in question will have an ideal final drive ratio, and the chain pitch in question (determined by the motorcycle power etc) will have an ideal sprocket radius and chain speed and momentum and so on, and as the loads and rpm changes you don’t want something that sets up harmonic or vibration in the chain, and you don’t want too much unsprung weight either, and the beat goes on.
So, on the one hand you have one of those n-hard type of problems that computers and spreadsheets are absolutely ideal for solving, and on the other hand you suddenly have a whole load more factors than just the simple chain link and sprocket teeth numbers that we started with, and these factors are much harder to integrate into that 3d surface that we generated in the basic version.
You are probably kind of back at the place where you’ll simply go 158/48=3.291666666666667 fuck it that is good to go, and 158/17=9.294117647058824 that is even better, fuck it, job done.
The important factor here is though it is theoretically possible to do the math and actually solve every single possibility for the problem for the next million cycles, the differences between theory and practice are greater in practice than in theory, it’s actually *hard* to do, because doing it means no fudge factors or approximations as they defeat the entire purpose of actually calculating the entire possible series of answers… so you not only have to codify everything exactly and accurately in mathematical terms, you then have to integrate it so it works as a whole, and does not give you any pentium floating point errors or excel floating point errors (doesn’t matter than the sum itself is all integers, the computer and spreadsheet will still floating point that bitch, because binary…) so you need another layer for verification and checksums.
Which is why you don’t actually know how fast the brand new Ferrari is until you put the actual car on a test track and time the bitch.
Which brings me on nicely to dyno curves, all anyone looks at are the peak bhp and torque numbers, which is intriguing, because the fastest car on the track is the one with the greatest area under the curves, not the one with the highest peaks.
The current 2002 vintage twinkie harley only puts out a measly 75 BHP peak at the rear wheel, utterly pathetic for a 1700 cc engine, though it compares very very closely on those numbers alone with the brand new M8 engines, nobody looks at the left hand side of the graph, 25 BHP @ 1,500 RPM, and simple math tells you that means a shade over 87 foot pounds of torque @ 1,500 RPM, and suddenly pennies start to drop, despite the tractor weight and tractor engineering, it’s a lot harder accelerating than anyone expects, while sounding like it isn’t really trying because the revs just aren’t there.
Nowadays there is all manner of fancy simulation shit for engine tuning, and I mean at a level that almost anyone can afford to buy and run on their PC, doesn’t have to be real time after all, like the chain and sprocket spreadsheet it doesn’t matter if it takes 30 seconds to compute each run… nevertheless nobody ever got a prize for a simulated engine output, you have to actually beat the other guy at the strip.
Dragstrips are another funny one, all anyone sees and all it says at the end of the strip are your terminal time and terminal speed, drag guys themselves only give a fuck about two things, reaction time and time for the first 60 feet… the other 1,260 feet depend entirely on those two numbers.
Everything I have discussed here is a simple problem, you know this is true because you can sit down and explain the problem to anyone, and they understand the questions being asked.
Everything I have discussed here *should* be solvable perfectly with computers, but explain why that ain’t so to people so they get it is much, much, much harder than explaining the problem itself.
But HOW and WHY did intel release a CPU that made math errors? HOW and WHY are spreadsheets prone to floating point errors in ways that are impossible on an abacus (but which can be explained on an abacus…lol)
It’s a no brainer that forecasts for western economies are done on computerised mathematical models that have much in common with a simple spreadsheet on your PC.
At 02:50 AM I find myself wondering if the powers that be also use some sort of computerised mathematical modelling to predict global scale political and social events, we *know* they do for weather H^H^H^ climate etc….
Perhaps, like the chain and sprocket example “the norks have 5.9 million paramilitary personnel, making them the largest army on planet earth… = don’t fuck with them, for any reason” is a fuck it, job done calculation, that cannot be improved upon by any more nuanced and detailed calculation that actually produced every answer for every iteration in the series?