HPT Dyno spreadsheet

This is an ugly one for now. It's very much an 'engineering sample' so tread with caution.

As about a week ago I was playing with MAP pressures, I had all this data in a spreadsheet, and it got me thinking: what if I had multiple columns and could somehow see the correlations between MAP, MAF, timing, etc on the actual acceleration. The problem is of course that I don't have any acceleration data. So I went after that first, thinking "if GTech can do it, why can't I?"

So I started with the assumption that samples are taken at 10Hz and then I go off the speed data gathered through speed sensors. Two problems:
1. I actually looked at the high-resolution timing and saw that the intervals weren't exactly 100ms
2. Speed data is given to us only with 0 decimal places

So you put the two together, and the precision of this system is bunk. often times you got two pieces of data that both show 73mph, while in reality is more like 73.1 and 73.9, but if you ask for delta of speed, you'll get a big fat zero. That's no good.

Back to drawing board, take 2:
1. Take the timing from the high-resolution timing ticker and use it to get 'composite' time in milliseconds, kinda like unix-style time. The problem with this is that Excel knows about hours, minutes and seconds, but apparently has no clue about milliseconds. Thus, we're doing this old school: parse the string for 4 parts, and calculate it all back together to make it into the composite millisecond time (who cares what it is, as I use it only for intervals anyway). Ugly, but fairly straightforward, too bad it takes a lot of screen real estate.
2. Velocity--to get this sucker in precise manner is a bit tricker. I decided to go off RPM, as I hope they got precise numbers as far as fireing a spark goes :)
So you take your RPM, tire size, gear, final gear and you get velocity. Great, but now you line them up with the velocities from the sensors and suddenly it doesn't agree. what's different? gears are pretty un-variable (is that even a word?), so probably the tire diameter is off, as it reacts to pressure, temperature, and of course it's rubber squished with 3500lbs of mass so it has good potential of changing. So of course I go by the go adage of "life gives you lemons make lemon vodka out of it" and use the discrepancy to find the more realistic tire size.

How do you do that, you might ask. Well children, you take your calculated velocity and you subtract that from the speed indicated by the speed sensors, and make a separate column for it. then you sum up this column. The idea is that to keep the sum zero, as an indicator that the variances were equal on both sides. Then you use Goal Seek from Excel, and you tell it to make that variance sum to be zero, by playing with the tire size. You'd be amazed how well this works!

Ok, so now that we have rpms, gears, and good tire size, time to get velocity with some precision, finally!
The next step is instantanious acceleration, as a function of dV/dt. Of course there must be some conversions, as your speed is in miles an hour, but your time is in milliseconds. Having an Eurotrash background, I of course pick the SI units for the middle ground, and end up with acceleration in m/s/s, which actually ends up with quite descriptive and easy to remember numbers.

As you look at most 'official' dynometer outputs, you will see 'smoothing' with a value attached to it. I always wondered why that was, until I actually threw some real data at my spreadsheet and saw how jerky the acceleration graph was! So in a general theme of copycating everything in sight (GTech, dynos) I decided to have smoothing of my own. All I did for that is to use bigger intervals, not the ~100ms that it normally gives you, but 'hop' over few cells of data, so you have speed variance over let's say ~500ms divided by ~500ms itself. So it's kinda a moving average I guess. I did it for 4 different smoothing levels, using 1,3,5,7 cells at the same time. Using the 5 cell spread is the most useful I think, seems to almost have the consistency of 7 cell spread, but without eating up too much data.

The best part about this spreadsheet is this:
you get to see how long (in miliseconds!) it takes you to go through a given interval of speeds! This is exactly what all car geeks always wanted: effective not theoretical, and 'area under a curve' not peak numbers! This is about as real as it gets, folks.

There are a few pitfalls/unautomated things:
1. I would like to pick an automatic range of speeds I'm interested in and get stats on that range specifically. (like time, for now I just look up speeds manually, copy the related values for time and do subtraction on them).
2. Automatically changing gear ratios for the calculations, probably based of acceleration dropping off.
3. Automatic recognition of what gear/tire/trans you're using based on rpm/speeds reported.
4. More statistics (so far I do min/max/avg/sum and calculate Injector Duty Cycle on the fly)

Well, it's allmost midnight, I'll put up pictures and such later, for now , this is just the link to the spreadsheet with some example data in it.

DOWNLOAD:
EXCEL (209k)