Setting up a PC-based spreadsheet to hold your important engine math formulas is a handy way to keep them all together and use them to calculate anything you want to know just by inputting the appropriate values. This assumes that you already have a core level of personal computer skills, and are able to open, manipulate, and save files without distress. A spreadsheet is nothing more than a big graphic calculator that you can easily program to calculate and analyze data. It is your own personal database, and you can configure it to make all the calculations in this book by inputting the appropriate information. It’s a great tool for brainstorming engine combinations. The sample presented here is done in Microsoft Excel, which many people already own. There are also online open document spreadsheets in what’s called the computing cloud. They are equally good and pretty secure. If you don’t want to have to log on to do some calculations, you can use Excel or any number of free spreadsheets available on the internet for downloading.
This Tech Tip is From the Full Book, PERFORMANCE AUTOMOTIVE ENGINE MATH. For a comprehensive guide on this entire subject you can visit this link:
SHARE THIS ARTICLE: Please feel free to share this article on Facebook, in Forums, or with any Clubs you participate in. You can copy and paste this link to share: https://musclecardiy.com/performance/build-engine-math-spreadsheet/
All spreadsheet software operates the same way. When you first open a new file you find a full page of blank rectangles called cells. They are organized into numbered rows and lettered columns. If you scroll down or across the page, you’ll find that it seems to go on forever. You won’t need all that space, but it’s nice to know it’s there just in case. Each cell can relate to any other cell or multiple cells depending on how you manipulate them. Cells can hold plain text, which can describe what is happening with data in an adjacent cell, and they can perform spectacular calculations in a heartbeat. This allows you to build small combinations of cells into stand alone calculators that you can label and tag with titles and the names of various values that are being input for calculation. You can arrange or group the cells any way you wish to personalize your own engine math calculator.
You can also put different types of formulas on different pages to keep them separate just like the chapters in this book. I’ll show you how in a moment, but first you need to learn a few more things about spreadsheets. At the top is a blank bar called the formula bar. You can input values at the formula bar or directly into the cells. When building a formula, it’s best to select the appropriate cell by clicking on it and then building the formula in the formula bar. If you enter something directly into the cell, it also displays in the formula bar as long as you have that cell selected.
When you enter a formula into the formula bar it remains resident in the cell you have selected, but only displays in the formula bar. The answer that the formula calculates displays in the selected cell. Cells appear blank until you enter data in the form of text or a numerical value, or until a resident formula makes a calculation.
Formula calculations are based on data that you input to other cells that are appropriately labeled to reflect the type of data being input. A formula may involve a simple multiplication function, or it may incorporate multiple functions like many of those in this book. Each formula must be entered in an exact sequence or it won’t function. The good news is that you only have to get it right once. After that it will always work because you’ll only be inputting values to other cells that the formula cell refers to for its calculation. If you can’t get it to work, consult the help screen or the manual. It is usually a simple missed keystroke that prevents the correct calculation.
If you have to make adjustments to the formula, do them in the formula bar. Keep in mind that the formula usually refers to more than one cell for its input. Cells are referred to by their location. For example, the seventh cell down from the top in column A is called A7. Another cell on row 7 might be selected under column D. That would be cell D7. If you input a formula into cell A1 that tells it to multiply the value in A7 by the value in D7 it will do so and display the answer in cell A1 as long as there are values entered in the two reference cells. The formula in A1 remains hidden. Only the answer shows.
Here’s a simple example (see facing page or page 141) using the formula for engine displacement: Recall that displacement equals bore2 x stroke x 0.7854 x the number of cylinders. Let’s find the displacement for a 350 Chevy whose bore is 4.00 inches and the stroke is 3.48 inches. You can build your calculator anywhere on the page, but for now let’s stick to the upper left-hand corner. Begin with row one, column A.
Select cell A1 by clicking with your mouse and type the word “Displacement” into the cell or the formula bar. Note that it displays in cell A1 and because it is too long, it overruns the adjacent cell (B1) to make one long cell for the word displacement. You can make a title like this anywhere you want. Underneath the title we are going to name the input values for our displacement calculator. They are all in column A.
Type the word “Bore” into cell A2. Note that any cell without an embedded formula simply displays what you type into it. It can be a name, a number, or whatever.
Now move to cell A3 and type “Stroke.”
In cell A4 type “Cylinders” to represent the number of cylinders in your engine. The input values for these cells are entered directly opposite them.
So right next to bore in A2, type the bore dimension “4.00” into cell B2.
Move to cell B3 and type the stroke value “3.48.” Then type the number of cylinders into cell B4. In this case it’s 8. Now you see that column A has labels and column B is where you input the appropriate value for each label.
Move to column C and type “cid” into cell C2 to represent the displacement.
Type “Cyl. Vol.” into C3 for the cylinder volume. Now you have column A for naming the inputs, column B for entering the inputs, column C for naming the answers, and column D for displaying the answers. You have already input the appropriate bore and stroke and number of cylinders in column B. All that’s left is to enter the correct formulas into D2 and D3. Formulas usually start with the equal sign (=) followed by cell references and the necessary mathematical symbols. There’s a way to input a number squared, but for now we’ll do it the long way to illustrate the process:
Select cell D2 and type the following formula into the formula bar: =B2*B2*B3*0.7854*B4. Note that the star key above the numeral 8 on your keyboard is used for the multiplication sign. In the formula you just typed, you are telling the computer to multiply the value found in B2 times itself times the value in B3 times 0.7854 times the value in B4.
After typing the formula, hit enter and the formula will reside in cell D2. Since you have already input values for it to refer to, it calculates the displacement and displays it in D2. The answer is 349.85 ci. (Later when you are more comfortable, you can go to the menu bar on top and adjust that cell so it displays two or more decimal places or simply round off the answer.)
Now move to cell D3 (opposite where it shows cyl. vol. in C3). Select cell D3 and type =B2*B2*B3*0.7854 into the formula bar. Note that it is the same formula, minus the value for the number of cylinders. That’s because we want it to show the displacement or volume of just one cylinder in D3.
Now the beauty of the spreadsheet is that the formulas in D2 and D3 remain resident in those cells, and they recalculate and display a new answer anytime you change the input values in column B.
To try it, select B2 for the bore. Delete the “4.00” and enter “4.03.” Leave the other two entries in column B the same. This gives you the displacement for a 4.030-inch bore with the same stroke. The answer is 355.12 ci. You should see the answer in D2 change to 355.11 and the answer in D3 change to 44.39.
Now you have built a simple displacement calculator.
Finding Piston Speed
You can do the same for the compression ratio formula or another formula elsewhere on the page or on a different page. To take it a bit further, let’s say you are playing around with bore and stroke combinations and you are concerned that the piston speed might be too high if you use a longer stroke. You can add that little bit of information right into your displacement calculator and see the piston speed at the same time as you view the cylinder volumes. All you need to do is add an engine speed value and a formula for piston speed.
Move down to cell A5 and type “RPM.”
Then, move over to cell C5 and type “PS ft/min” for the piston speed in feet per minute.
Enter the formula for piston speed into cell D5 using the formula bar.
Piston Speed in ft/min = stroke x RPM ÷ 6
So you enter: = B3*B5/6
Now the piston speed for any stroke shown at B3 displays at D5 and it changes anytime you enter a new stroke dimension into B3. Cool, huh? A more advanced problem would be to calculate an unknown bore size when all you know is the stroke and the displacement. Recall the formula from Chapter 1:
Bore = √[engine size or cid ÷ (stroke x 0.7854 x number of cylinders)
] In this case: √[350 ÷ (3.48 x 0.7854 x 8)]
To find this in the spreadsheet, type “Displ.” into cell A8. That indicates you are going to enter a known displacement into cell B8.
Now type “Bore?” into C8 to indicate that the calculated bore dimension displays in the adjacent cell D8.
Type “Stroke” at A9 so you can enter a reference value at B9.
Select D8 and enter the following formula into the formula bar. It is the spreadsheet version of the formula for finding the bore: = sqrt(B8/(B9*0.7854*B4))
Now you can enter a known engine size at B8 and it displays (in D8) the calculated bore size based on the other reference values. You can arrange your input and output cells any way you want, as long as the appropriate cells are referred to in your formula entry. Note that (sqrt) in the bore formula tells it to take the square root of the references within the parentheses.
Squaring a number in a formula can be done in two ways. If you have 4.00 in cell B2, you can square it in the formula bar as follows:
B2*B2 (which means 4.00 x 4.00)
Or you can do it this way: B2^2 (which means B22).
The little arrow character pointing up tells the formula to raise the preceding value to the power immediately following it. It is called a caret, and it is the symbol for raising to a power. The number immediately following it indicates the required power. In this case the 2 after the caret tells the formula to raise the value in cell B2 to the second power. In other words, square the preceding value. If you were to put a 3 after the caret it would tell the formula to cube the B2 value, or the same as B2 x B2 x B2.
Finding Stroke Length
Suppose you have a displacement limit and you have decided to run the largest possible bore to help unshroud the intake valve and to run maximum piston area. Say the limit is 372.99 ci and you are planning a bore size of 4.125 inches. How do you set up the spreadsheet calculator to find the stroke that keeps you within your limit? First, ignore the 0.99 and just go with 372 to have some wiggle room to accommodate bearing tolerances and re-honing. Recall the formula from Chapter 1:
Stroke = displacement ÷ (bore2 x 0.7854 x number of cylinders)
Stroke = 372 ÷ (4.1252 x 0.7854 x 8) = 3.479 or 3.48 inches
All of the necessary data is present on the sample spreadsheet we have already constructed.
Step 1 :
Enter “stoke” at cell C11.
Step 2 :
To add a separate reference for this bore dimension, type “Bore” into A12 and use the input bore at B12 for your formula.
Step 3 :
Now select D11 and enter the following formula in the formula bar:
Step 4 :
The result displays in D11. It tells you the maximum stroke you can run to stay legal.
This takes longer to explain than it does to actually do. The key is to remember that all of the formulas you enter have to refer to input cells that contain variable values that you can change. If the formula contains a mathematical constant, you can type it right into the formula. It doesn’t have to reside in a reference cell even though you could do it that way. On a hand-held calculator you can just enter 0.7854 and it knows what you mean.
Refer to the symbols chart below to build the spreadsheet versions of the formulas presented in this book. The most useful way to apply these formulas is to build them into individual calculators based on the formulas found in each chapter. One way to do it is to group some of your favorite calculators on a single page and have the reference cells for all of them shown in columns A and B. Then you are always changing values in column B and reading the results elsewhere on the page within the individual calculators. That way, different calculators that sometimes require the same information all refer to the same input cell and you only have to input the value once.
You can also put different types of calculators on different pages. The following is a suggested list of the types of calculators you might want to create on different pages:
Page 1 displacement, compression ratios, and piston speeds
Page 2 horsepower, torque, and RPM formulas
Page 3 induction, cylinder heads, and exhaust formulas
Page 4 fuel systems, atmospherics, and combustion
Page 5 camshaft math
Page 6 other handy formulas
The following symbols will help you build your formulas in the formula bar:
= formula follows
– subtract sqrt take the square root
^ raise to the following power (e.g., x2 = x^2 and x3 = x^3)
( ) contains operations to be worked as a group
Once you get the hang of it, you’ll find ways to incorporate all kinds of formulas besides those used for engine math. They might include gear ratios, trans ratios, wheel speeds, suspension travel, or whatever. Soon you’ll be saving all your favorite formulas on your PC. Just don’t forget to save your work each time you use them. If you don’t have Microsoft Excel you can purchase a copy online for a decent price. It’s worth it because it can calculate any formula in this book and much more. It’s available for PC or Mac and well worth the expense. Eventually you will also figure out ways to build other calculators and even turn them into databases for parts and equipment—perhaps make your own engine build sheets with blank spaces for entering specs and clearances. The possibilities are endless and if you can’t afford Excel, there are always open document versions on the Internet.
Written by John Baechtel and Posted with Permission of CarTechBooks