Updated spreadsheets

[msd1107]

There are frequent questions from members about the influence of tire size, differential ratio, gearbox ratio, and RPM on one another and speed obtained. There are six ways to display tables of two of these variables. A previous version allowed input variables in English and metric units. Inexplicably, the output was always in English units. Very poor. The user can now specify the desired output units. In addition, some users use the value MPH/1000rpm. This has been added also.

The standard car comes with speedometer turns/mile and transmission speedometer drive gear to properly display miles driven and speed for the standard tire and differential combination. Input the tire size, differential ratio, and speedometer turns/mile figure and the program will display the closest transmission speedometer drive gear for your differential ratio. There are three drive gears available for the Ford transmission. Your current car configuration may require a speedometer drive gear that is not available. In this case, specify your current transmission drive/driven gear and the program will display the closest gear pair for the speedometer. Most dealers that rebuild Smiths instruments will be able to rebuild your speedometer with the best gear pair. It is theoretically possible to replace the gear yourself.

The last spreadsheet allowed the input of basic car parameters (weight and size) and a variety of modifying parameters and would calculate the power required at any speed. In light of the interest in vehicles? energy consumption and the interest in various battery propulsion techniques, the spreadsheet has been expanded. If you know the fuel consumption at any speed, the sheet will return the engines? thermodynamic efficiency or vice versa. Given the distance traveled, the program calculates the energy consumed.

For a pure EV, input the capacity of the battery in KWH plus a variety of other parameters. The program will calculate the theoretical range at any particular speed plus the energy consumption for a given distance driven. Vary the values in B65, B69, B72, B73, B74, and B75 to see their effect on theoretical range and energy consumption.

For a PHEV, it helps to have nominal fuel consumption figures before the plug-in conversion, although that can be back calculated (exhaust the battery, measure the fuel consumption to get the thermodynamic efficiency. Put that in B81). Input the capacity of the battery plus other factors and the manufacturers advertised fuel economy figures at speed. The program calculates the actual engine fuel economy in F80 and total energy consumption. You can see how that changes when varying the values in B69, B72, B73, B74, and B75.

This spreadsheet starts from fundamental physics and mathematics, together with a series of modifying (and often estimated) parameters to calculate horsepower required, energy consumed, and other results. If the modifying parameters are accurately specified, the calculated results conform to actual measured performance. If the measured performance does not correspond to predictions, examine each of the modifying parameters to determine which of those are inaccurately specified. Or use Excel goal seek, or modify parameters on an ad hoc basis to determine what parameter values are needed to reach desired performance levels.

As before, the spreadsheet is too large to upload directly to LotusElan.net. So it is compressed (ZIPped). However, LotusElan.net does not allow .ZIP files, so the files is renamed to a .XLS extension. After you download the file, rename it back to its .ZIP extension, then extract the .XLS file. Comments etc are solicited.

David
1968 36/7988

PS: Scroll to the botton of this topic to get the latest version of the spreadsheet.

PPS, I deleted the link to the original spreadsheet since readers were not scrolling to the end of the post to get the latest spreadsheet. 20070526zDragSpeed.xls

[peterako]

Wow!

Thank you David!

You put some amount of work in putting that sheet together!

Take care,
Peter

[msd1107]

Well, changes all the time. The F1 regulations for KERS specify capacity in Joules, not kwh. So if you are modeling HEV, PHEV, or EV type vehicles, you can input the battery capacity in whatever units you want.

As before, the spreadsheet is too large to upload directly to LotusElan.net. So it is compressed (ZIPped). However, LotusElan.net does not allow .ZIP files, so the files is renamed to a .XLS extension. After you download the file, rename it back to its .ZIP extension, then extract the .XLS file. Comments etc are solicited.

David
1968 36/7988

PS, I deleted the link to the spreadsheet since people were not scrolling to the end of the post and downloading the latest version. 20070526zDragSpeed.xls

[msd1107]

More updates.

Energy consumption is now shown in BTUs, MJoules, and the equivalent number of gallons of gasoline.

For an EV, net fuel economy now shows an equivalent MPG figure. It is interesting to look at this figure in comparison with IC and HEV vehicles. Also how changing assumptions about various efficiencies in the power chain affect the equivalent MPG. Also, the effect of various speeds on the effective MPG.

The help text for PHEV and EV is expanded substantially.

As before, the spreadsheet is too large to upload directly to LotusElan.net. So it is compressed (ZIPped). However, LotusElan.net does not allow .ZIP files, so the files is renamed to a .XLS extension. After you download the file, rename it back to its .ZIP extension, then extract the .XLS file. Comments etc are solicited.

David
1968 36/7988

[mariodschy]

Hello,

i found this interessting thread but i can not open the "20070526zDragSpeed.xls" file, excel says that the file ist broken...
Can someone atach this file as a actually readable file (maybe as a xlsx).

Thanks
Mario

[rcraven]

Do what the instructions say and change the file extension.

[mariodschy]

thanks, it works...

[GreyElan]

Here is a really useful tool that does the numbers and gives the data visually. https://www.blocklayer.com/rpm-gear.aspx

I have done the numbers on a standard Elan box with both 3.0 and 3.3 CWP which are apparently readily available from TTR. I have attached a PDF with the output. The effective 1st gear with the 3.3 (compared with a 3.54)is 2.77 and the ratio spread is very useful and at 3120 rpm you have 100kph. The effective 1st with the 3.0 is virtually identical to the Close ratio box with very relaxed cruising of 110kph at 3120 rpm .

I will probably go for the 3.3 and switch between CN36 175 x 70 x 13 and Dunlop R 175 x 60 x 13 tyres to suit my purpose.

I have run just about every T5 permutation through this app and, bell housing aside, if I go this route I would ask the gearboxman to build a "Cosworth close ratio Kit" into the case/shifter configuration that they use for Jaguars if possible. This should mean that the box could be removed without the engine with a quick release bell housing setup as there is effectively no tail housing. A 3.77 would give 100kph @ 3030 rpm. Relative 1st is 3.1.

Cheers Peter

ELAN box with 3 and 3.3 diffs.pdf

(1.29 MiB) Downloaded 1002 times