More spreadsheets

[msd1107]

Well, I was over at a fellow Elan owners house looking at his car. As usual, we got into trouble asking questions for which there was no readily available answer. The basic question was what speed will the car go for various differential ratios. The other question was what is the theoretical top speed of the Elan (theoretical since we know the car is so under geared it can't get close)

Many of you know I have this monster spreadsheet that can give some of this information, but only a piece at a time.

Getting home, and awhile later, I had adapted the original into a tool for answering the question. Input the rpm and tire data, and is has a two dimensional matrix of R&P values down the left and gearbox ratios across the top. The R&Ps are all the combinations of pinions from 7 to 11 and ring gears from 31 to 41. I mean, if you can present one result, why not choke the world with more results than can be assimilated or used?

Since this Elan was running with low profile tires, I figured it would be useful to have a sheet that gave speed values for different tire sizes. So quickly I made a sheet that extracted the tire size data from the big spreadsheet, and used that with different gearbox ratios to display speed given rpm and diff ratio.

This was so much fun, I made another sheet that showed speed for different R&P values and different rpms, for a constant tire size and gearbox ratio.

Well, there are 6 ways to display the variables, but I stopped here. If anyone wants any of the other 3 ways, I'll whip up the sheet, but this spreadsheet is getting close to 1mb, although it zips down nicely.

The last thing was to generate a sheet that would estimate the top speed of a car. Now, this is a very slippery area, since many of the factors involved in the calculation can only be estimated, and the estimates have a rather large margin of error. And there are many ways to agglomerate all the aspects that go into calculating vehicle drag.

So, given all those caveats, the first cut came up with some plausable lies (estimates). I would be most interested in others experience in this area, suggestions for improvement, comments on how I goofed up, etc.

Have fun.

David
1968 36/7988

[msd1107]

Well, as usual, if a little is too much, then a lot is far better.

Added a sheet that allows you to see speed for different tire sizes and differential ratios. This has quite a few entries, so the instructions show how to hack it down to a manageable size.

Added in to the drag calculation the ability to take into account up to 6 factors in addition to the the basic body size and Cd. But, as usual, you can bypass the detail, just enter one figure and look at the results, and refine as you desire.

Unfortunately, the spreadsheet has grown, and I don't want you to suffer the time downloading the uncompressed file. So I ZIPped it. However, LotusElan.net does not allow .ZIP extensions, so I renamed the .ZIP to .XLS. When you download it. rename it back to .ZIP and unzip it. I hope this subterfuge works!

David
1968 36/7988

[msd1107]

Well, interesting sets of responses, some from areas in which I would not have expected. I thought it was interesting to play around with different aspects of a cars' aerodynamics and see what effect that had on horsepower requirements.

But I have been having comments from people playing with the other spreadsheet that designs supposedly optium gear ratio sets. (You have to email me for that one, it is much too large to post). In any case, the debate has been over exactly what is the optimum design for staging the gear ratios in a gearbox. With some interesting proposals. But no way to model them for appropriatness, except with the mathematical Figure of Merit the spreadsheet uses.

Then the suggestion was made (quite innocently I think) that the drag spreadsheet be expanded to calculate acceleration times to speed so we could see the effect of gear ratio staging on acceleration times.

Fortunately, the recent Club Lotus Magazine article on putting EFI on a TC included a good power curve.

So I added a section to input a power curve, and populated it with the power curve from that article.

You can input flywheel weight, wheel/tire weight, and prop shaft weight and the program adds in their rotational inertia when calculating acceleration figures.

You can specify up to 7 gearbox ratios and the program will calculate the instantaneous acceleration for all ratios for a range of speeds you specify, pick the gear with the greatest acceleration and build a table of incremental and cumulative acceleration times.

I haven't had a chance to debug this thoroughly or play very much. But a couple of things pop up. One is how much rotational weight the wheels/tires add at high speeds (400+ lbs at 120mph) and a normal flywheel adds (300+ lbs at 7000 rpm)

It turns out the two easiest ways to increase the acceleration between 10 and 120 is to replace the mechanical water pump with an electric one (an incredible 3.5 sec 10-120) and lighten the flywheel (saving 8 lbs saves 1.6 sec 10-120).

It turns out that some guys are associated with the Playstation GT4 people, and have done some of the same simulations, with the same results. But GT4 has 150 programmers and we are a lonely group.

So in any case, download this, and play with it, and tell me what is wrong, or what could be improved. As I stated in the previous post, the spreadsheet is too large to distribute, so it is ZIPped, renamed to a .XLS before posting. Download it, rename it to .ZIP and unZIP it.

Have fun guys.

David
1968 36/7988