gears, powerbands and shift points

Long time ago, I read THIS.
It really made me think, especially that Andi is a _really_ smart dude.
For the longest time I've been pondering how do I write a program that would take gearing, tires size, shiftpoints and come up with the most optimal configuration. In video games (like the Gran Turismo series) you always end up with a 'magic' trans with which you can set up just about any combo you'd like. I spent hours (yes, I know, it's sad, shut up) tweaking it so the top speed on the track would match the redline in last gear available. Then I discovered that depending on the track first gear would have to be long enough that it wouldn't be useless (aka permanent spinning) or short enough that I could just ignore it and don't use it for anything but standing starts.
In real world, we have slightly different problems. We can't change internal gearing of the trans (well, we can, but that's pretty much left for real racing teams and people who really don't have a budget), we can only pick from a limited selection of final gears, and we also we have to watch out for things like not using overdrive for racing situations as it's too stressful. (Doesn't Porsche 911 turbo have like 3 overdrive gears though? Well, I guess that trans can take it, you get what you pay for). So in my case, I got the GM 4speed automatic with lovely 3.06,1.62,1.0,0.7 gearing. These are HUGE drops in between gears. With stock gearing it would shift 6000->3200, then 6000->3700 which totally made it fall off the happy range. First thing was to bump up the rev limiter to 6600rpm and set up shifts for 6200rpm, which depending on temperature and how much I've been beating on it would do it between 6200-6400rpm.
that was slightly nicer, after a shift I'd end up 200-300rpm higher than before. That's not enough however, so I had to go out and get a 3.73 gear :) With that, and the raised shiftpoints, it would shift 6300->3500 (1-2) and 6300->4000 (2-3). That made it much snappier, 2nd gear would shift out at 72, not at 90, 3rd wouldn't go past 140, it would hit 6500rpm at 125mph. It's a much more track oriented setup now, makes for a good daily driver too.

But of course, me being me, I didn't wanna go by seat of pants, I wanted some numerical backup of what it does and by how much.
So all these things combined, lead me to create a spreadsheet that takes into consideration different power curves, gearing, tire size, and shiftpoints and then counts exactly how much work was done in each of the intervals. The problem was to figure out that I had multiple gears with multiple power figures for each rpm and each gear, and I also wanted separate shiftpoints, as you saw at Andi's writeup, it can vary quite a bit.

So here it is: DOWNLOAD

Put in your data and take a look at the average hp rating. Start playing with it. Put in shorter gears, up your shiftpoints, watch the numbers change. I'm gonna post some useful scenarios for which this spreadsheet is useful later.

If you wanna know what's behind the scenes, I've basically taken the HP numbers relative to the gear they're in (yes, you can have separate maps for different gears, all you turbo people should be happier!) and added them up as long as fall fall within rpm range (bigger than the 'landing point' from the previous gear, and smaller than the shiftpoint). These are the points highlight in orange. Under columns tagged W you got a sum and a count of the 'valid' cells for each gear. Then on the right you got it all summed up.

That's where 'interpretation' problems start. Because depending on gear one 100rpm cell can cover more mph, once you end up counting them up you might end up with a different number of cells counted, even though the speed range through which you went was unchanged. Change gears from 2.73 to 4.11 and I can just about guarantee you're gonna see it happen. So what's the problem? Well, you if you add up more cells, you're probably gonna end up with a higher sum, making the comparison moot. So there's two ways of going about this:
1. somehow 'normalize' the hp values according to rpm/mph factor of each gear (there's already some values for doing this, on top of Range colums)
2. go by the average hp per cell values. That's in red on the right. Seems to work fairly well, but I'd rather do it with the first method.
Let me know what you think.