Hello everyone. Hope you are all well?!

Anyway, enough of that. I am trying to predict the performance of my Reynard using a lovely little spreadsheet, hoping that it will show graphically the tractive effort in each gear at speed and the speeds in each gear. Simple. I've done that and it works for any car and gearing. Hurrah!

The problem is thus: I want excel to be able to tell me what the optimum shift points are (i.e. where the tractive effort curves cross), and what the theoretical top speed is (i.e. where the drag curve crosses top gear). But my excel skills are not sufficient to do this with the discrete engine torque data that I have (every 250rpm from 3000 to 6500rpm).

As far as I can see there are two possible solutions:

1. Use interpolation on the values in an extremely clever way so that it can work out the approximate points. I have no idea how to do this despite reading a few articles on the 'net.
   
2. Generate an equation that 'best fits' the discrete torque curve, and use that equation for all subsequent calculations. I've tried to do this by trial and error and the results weren't pretty (and would take forever to update to a tune or a different car). Isn't there some method of adding different sine curves together to reach a complex curve? I've also tried using Excel's line of best fit using a 5 order polynomial and feeding the resulting equation back into excel, but it's not accurate enough it seems (the results were so far out it was a complete waste of time trying).

I'd like to avoid 'witty' suggestions of use a different program or make a program in VB to do this, as I'm sure a clever excel/maths boy can tell me how to do it.

Comments would be useful, especially from brainboxes like Bob who have already made similar tools (albeit by actually programming something).

Thanks muchly, and I look forward to all the silly suggestions I shall no doubt receive. I'd do the same if you were writing this so...

You may have already read it, but: http://en.wikipedia.org/wiki/Interpolation

Generally, if we have n data points, there is exactly one polynomial of degree n−1 going through all the data points. The interpolation error is proportional to the distance between the data points to the power n.

Having cited this: Can Excel only fit with polynomials of degree 5?
If so, then yes, I guess you'd need other programs for that..

Not sure about freeware products, but the professional mathematics programs like Mathematica and Maple will probably do the job.

Sorry for not having the answer you wanted to hear, though.

Not easy, polynomial curves don't tend to look much like real torque curves. Like the post above states, you need a polynomial of order 14 to fit to your data? Just because the Excel auto-polynomial thing limits you to 5, doesn't stop you doing it manually. Although GRC only uses order 3, I want to up it to order 4 and the maths for that are like /me whistles (ok it's essentially the same maths, only it all gets much more complicated). So doing 14 manually probably isn't a sensible challenge.

Combining sine waves sounds promising, but how to use that method to fit data I have no idea.

Alternatively, split the data into 1000rpm data sets, and you should be able to get a polynomial to fit each? Then just string them together? An idea.

There's a little proggy called CarTest that can do that for you. You just put in the specs and it works it all out. You can get version 4.5 here. Hope it works for you.

I did that once in Excel once and actually got it working after finding out how to calculate high order polys in Excel somewhere on the net. But is was a big mess and I can't find it anymore. It was somewhat similar to these:
http://www.excelformeln.de/tips.html?welcher=13
http://www.excelformeln.de/tips.html?welcher=39

It's a German site but you can just click on download to get the spreadsheets so that you can have a look at the Formulas. After looking at them you will problaby lose your interest in the project.

I think using other software than Excel would be the clever thing to do and the practical thing would be just looking at the graphs in Excel and manually reading the shift-rpms without any calculations.

Maybe not what you're looking for but i often use CarTest to test for changes on my car.
I've also tested using a G-tech and the values are very close. Recently i used it so my
friend would get an idea of the performance he can expect from his engine swap. Once
he's done, we'll be using that G-tech again to see how close CarTest was.

http://www.cartestsoftware.com/cartest4.5/index.html

Or you could have just read my post and saved yourself the trouble of posting, Fonnybone.

Quote from Bob Smith :

Just because the Excel auto-polynomial thing limits you to 5, doesn't stop you doing it manually. Although GRC only uses order 3, I want to up it to order 4 and the maths for that are like /me whistles (ok it's essentially the same maths, only it all gets much more complicated). So doing 14 manually probably isn't a sensible challenge.

It isn't actually very difficult to program a regression analyser if you can invert a matrix. I've written several programs in Scilab (a free Matlab clone) to do this.

One of my colleagues at work came across a similar interpolation problem which our resident Excel guru couldn't solve easily. A few minutes spent in Scilab and I had his problem solved.

I know you didn't want suggestions for other programs, Tristan, but unless you can code VB, Excel is fairly limiting in this area. You'd almost certainly be better off learning a more advanced language.

Out of courtesy I should have thanked you all for your time a couple of days ago. I found a little website (which I've already forgotten the URL of) that had examples of how to do it with embedded macros via VB code. So now it works a treat.

Next thing on my list is to allow different gear ratios to be compared side by side, with the area under the tractive effort graphs shown. But this is relatively easy and can wait...

Ooh, right up my street. You need help with acceleration timings, per chance?

Maybe. Soon. But not right now.

I shall pick your brain in the coming week or two