Welcome to the first video in the three part Excel Training for Engineers. In this video, we’re going to look at some simple techniques to minimize Excel frustration.
My name is Charlie Young, I’m a Professional Engineer and my background is in mechanical engineering. I’ve been fortunate to have a great career in engineering. That’s because I’ve been able to solve challenging engineering problems that have helped my employer build better products. I’ve learned to use a lot of engineering tools to solve these problems over the years, such as finite element analysis software, computational fluid dynamics tools, system modeling software, and more. But the most important tool in my toolbox has been, and probably always will be, Excel.
Over the years I’ve gotten pretty good with it, but it hasn’t always been that way. When I graduated from college and got my first engineering drop, I found that the more senior engineers in my company were using Excel for everything. I quickly received that the Excel training I received in college didn’t prepare me for this at all. Now I could do simple calculations, create basic tables and even some charts (mostly scatter charts, but some bar, column, and line charts too). But I saw other engineers doing this really advanced projects with Excel and wanted to learn how to do them myself.
Now the engineers with Excel experience at my company were always very busy, so I had to learn on my own through trial and error and yes, lots and lots of Google searches. Needless to say it took me a long time to learn this way. I tried a few training courses, but they were for general office work and not geared towards the specific ways that engineers use Excel. The journey to where I am now with Excel has lasted over a decade, but along the way my simulations have been featured at multiple international technical conferences and I’ve helped thousands of engineers grow their Excel skills through my website, EngineerExcel.com.
Remember those senior engineers that built those amazing spreadsheets at my company? Now I’m even able to help them with the Excel stumbling blocks they encounter from time to time, and I want to help you too, so I put together this three part video series to give you my best strategies for setting up and solving complex engineering problems in Excel.
I’ll show you these strategies by walking through an example problem that deals with the lid of a pressure vessel. The lid is exposed to a pressure that cycles continuously between a minimum and maximum. This changing pressure results in fluctuations in the bolt forces and could result in fatigue failure if the hardware is not chosen properly. So we need to determine the hardware size to provide infinite life of this joint with a safety factor of three. Since this is a fatigue problem we’ll solve it using the Goodman line, which is defined by the endurance strength and ultimate tensile strength of the bolt material. In order for a design to meet the infinite life requirement, the operating point, which is defined by the means stress and the alternating stress, will have to be at or below this line, which runs parallel to the Goodman line and crosses the Y axis at the equivalent stress, which is equal to the endurance strength divided by the factor safety.
Now the reason why I consider this a complex engineering problem is that it involves a system of five non-linear equations, which we need to solve for A, the area required per bolt. We could get out a pencil and try to solve this system by hand before plugging it into Excel, but I’ll show you, over the course of three video series, how you can use Excel to solve this problem faster and with fewer chances of errors.
To simplify the problem a little bit, I’ve also made some assumptions. We’ll assume that the preload is equal to 1.5 times the maximum force. We’ll also assume that the bolts carry two percent of the joint load. As the joint tries to separate some of the load is carried by the pressure vessel components themselves and some is carried by the hardware. To make the problem solvable in a relatively short period of time, we’ll just assume the portion of the load and the bolts is two percent. We’ll neglect component deflection and assume that everything is rigid. We’ll also neglect the gasket, which I haven’t shown in the drawing. And finally we’ll assume that the joint uses Grade 8 fine threaded hardware.
Okay now that we’re in the actual spreadsheet, we can start taking a look at the calculations that we’re going to use to solve for the bolt diameter that’s required to meet the design and targets for this pressure vessel cap. Now, what I like to do when I go in and set up a spreadsheet, especially one that’s going to have a decent amount of calculations in it, is to have a separate section for my inputs, another section for my intermediate calculations and then a third section for results.
So as you can see I started here with an input section. It contains three columns. The first column is just a description of each of the input values. The second column then is the actual input values themselves. And the third and last column is a column for units, if there are any.
Now in some cases there won’t be a unit for the input value, like right here the number of fasteners is six, but it doesn’t have a unit, so I type an apostrophe and two hyphens, just as a reminder to myself that I didn’t actually forget the units, it just has no units on that value.
So, let’s go through each of the inputs. There are, like I said, the number of fasteners, the minimum and maximum pressure that this pressure vessel is going to be cycled in between and I’ve entered five megapascals and 25 megapascals. The bolted joint safety factor which is three, that’s safety factor that we’re targeting with our bolted joint design. The pressurized diameter of seven inches, and that’s just this diameter that’s right here underneath the cap, it’s going to be seeing that pressure cycle from five at 25 megapascals. A thread stressed concentration factor of three, that’s a pretty typical value for Grade 8 hardware, and also a bolt ultimate strength, which for Grade 8 hardware is 150,000 pounds per square inch or psi.
Now finally these last three are D rating factors and they come from Shigley and Mischke. They are factors that are going to reduce the endurance strength of the material based on the surface finish, the size of the hardware and the reliability, so we’ll use those in our calculations as well. Now these are all the inputs that we’re going to need for our calculations, so now we can go ahead and create a section for the actual calculations. I’ll just go over here and give this section a heading, we’ll call it calculations, and I can just go ahead and use the format painter also to apply that formatting to this section so we have a consistent formatting throughout our spreadsheet.
Okay, so the first calculation that we need to start with the pressurized area. All right, we’ll just call it A pres, and we’re going to calculate it from the diameter that we have right here in the input section. Now we could just go ahead and type the equation like this, so that’s going to equal Pi divided by four times the diameter squared. Okay? So that calculation is referencing this diameter cell which is C7. All right, but as the complexity of this spreadsheet begins to increase, the references to the cells are going to get harder and harder to keep track of. You’re going to have C9, C10, C11 and so on and then references to some of the intermediate calculations that we’re doing in this section in columns F, G and H.
So to help keep track of all these variables, rather than using cell references, we can actually name the cells and give them variable names that we can use in our calculations. So I’m going to hit escape here and then I’m going to go and select this pressurized diameter input cell, where I’ve entered a value of seven and I’m going to then go up to the name box here and I’m going to give it a name. I’m going to call it D underscore pres for pressurized diameter and then I’m going to hit enter. Now when I go back into my calculation to edit it, I can take this C9 reference out of here by just highlighting it, hitting backspace. Instead, in its place I can type in D underscore pres. So when I do that you can see that this little drop down is added right here and you can also see that I’m re fencing at cell C9.
One of the things that’s different about it though is that I don’t have all these little square drag handles on the corners of the cell selection, that’s because I can’t move it around, it’s actually locked in to that cell, which has been given the name, D underscore pres. So if I hit enter, now I get the same calculated result as I had before, but I have an equation that’s much easier to understand if anybody would ever come back into this spreadsheet and wanna understand how the equations are working.
So while we’re at it, let’s go ahead and add some names for all the other input values as well. So we’ll call number of fasteners, we’ll call that N underscore bolts. We’ll call the minimum pressure, P underscore min and we’ll call it P underscore min underscorempa to indicate that it’s the pressure in megapascals because later we’re going to have to convert this pressure in megapascals to a pressure in pounds per square inch, we’ll want to keep them separated. We’ve also got P max mpa. Bolted joint safety factor, we’ll call that SF. Pressurized diameter which we already did. A thread stress concentration factor, which we’ll call K underscore thread. The ultimate strength, which we’re going to call S underscore u or just SU rather. A surface finish derating factor, which we’ll call Ka. A size derating factor, which we’ll call Kb. And a reliability derating factor, which we’ll call Kc.
Okay so now we’ve given names to all these input cells. If we ever wanted to go back and change any of these names or change the cells which the names are referencing, we can do that by going up here to the formulas tab and opening the name manager and now you see a list of all these variable names that I just added in here and if I click on one of them, like n bolts, click edit, I have the opportunity here to edit the name, I can also edit the cell which it’s referring to. I’m not going to change anything here, so I’ll just click okay. You can also add a name through here, although it’s a little bit more complicated than just doing it through the name box. I could create a name cell for the pressurized area, call it A underscore pres and then I’ll just go ahead and select that cell that contains the value and now we have assigned that name to it.
If you ever want to check the name of a cell, you can go ahead and actually click on that cell and you’ll see the name show up here in the name box. I should also mention that when you’re naming cells, that you can’t use any spaces, so you have to use underscores in place of spaces. You also cannot name a cell something like A2 or P3, because that Excel is using as a cell reference internally. If you try to type that name into the name box, it’s just going to jump to that cell and won’t allow you to actually give another cell that name.
Now just like I did for my input section where I had a description, a value and units, I’m also going to want to do that in my calculation section so I can keep track of those values in the same way. So the units on here are going to be in inches squared.
Okay, so now that we’ve calculated that area, the next thing we need to do is take the area and the minimum maximum pressure and calculate a minimum and maximum force. But as you can see, we’ve got some inconsistency in our units. The minimum and maximum pressure are in units of megapascals, which are si units, the area is in units of inches squared, which is a standard unit.
Now early on when I was using Excel, I would always go out to a book or a piece of software or even Google to find my unit conversion factors, but I discovered that I can actually convert units directly within Excel itself using the convert function. So I’ll show you how that works here. We’ll calculate the minimum pressure. Okay? And I’ll make this a little wider, because we’re going to enter a formula in it, and then the units we’ll convert to are going to be psi and we’ll be starting in megapascals over here.
So the formula to do that conversion is just going to use the convert function. Okay it shows up there when I type convert, converts a number from one measurement system to another. Okay the first argument in the number. So that’s just going to be this five megapascals here. And we want to convert it from megapascals to psi. So we can do that in two different ways. We can just type in megapascals enclosed in quotation marks, okay? And then the to unit, which is psi or pounds per square inches, when you put that in quotation marks too, add our closing parentheses and hit enter, and the tricky thing there is it actually did an auto correct on mpa right here, it only capitalized the first letter and it really needs to be both letters capitalized, so when I correct that and hit enter, it does the unit conversion appropriately.
Rather than actually typing all that stuff in though, I can just go ahead and select the cells that contain the unit names and that’s one of the big reasons why I like to have the units in separate cells. So if I go ahead, the from unit is going to be megapascals, so I can just go ahead and select that, comma and then I can just select, I’ll just hit enter here and make this a little wider so I can select that cell, go ahead insert my cursor and select psi. Now it will do the unit conversion just the same and I didn’t have to actually type in the units, I could just select them in the cells.
I’m also going to go ahead and decrease the number of decimals here, because that’s pretty unnecessary and now we can actually do the same thing for the maximum pressure. That’s going to equal, convert function, select pmax, mpa, units of megapascals and the unit of psi, which I will enter in here shortly. Of course, I’m getting an error because I don’t have the units entered yet, but once I do that then it calculates the value.
Okay and of course because we’re going to use these values later on in our calculations, we’re going to wanna give them names, so we’ll call this one P underscore min, and this cell right here, we will P underscore max. Okay so now that we have everything in consistent units, we can go ahead and calculate that force, both the minimum and the maximum forces. So first the minimum force, call it F min, is calculated as the pressurized area times the pressure. We can go ahead and select those cells or we can just type the variables in that we assigned and hit enter to do the calculation. That of course is going to be in units of pound force. Likewise, we can also calculate the maximum force as the pressurized area, A pres times P underscore max. That’s also in units of pounds force. Again, we’ll create variables for those so we can use them in subsequent calculations. F underscore min and F underscore max.
Next we can calculate the endurance strength, which is the stress under which a component can be expected to have infinite life. So we’ll create a rule for that and go ahead and make this a little bit wider. The endurance strength for most steels is around half of the ultimate strength, so we can just type that formula in, 0.5 times the ultimate strength, SU, but since this is a bolt and we’re also dealing with some other things that are going on here that can reduce the endurance strength of the bolt itself, we’ve gotta add in these derating factors and those are just added in by multiplying so that’s sum times Ka, times Kb times Kc. So that gives us an endurance strength of 41,310 pounds per square inch and we’ll also give this a name, S underscore E.
Okay, so the last calculation that I’m going to do in this video is to calculate the equivalent stress that we need to target in order to reach that safety factor of three. And this is really the final thing that we need to calculate before we can get into solving that system of non-linear equations to calculate the bolt area that’s required.
So we’ll create a row for bolt equivalent stress, and we’ll call it sigma Eq and the formula for that is the endurance strength, which we calculated up here, Se divided by the safety factor, Sf. Okay so we have a bolt equivalent stress of 13,770 psi.
So that’s how you can use named ranges and the convert function in your spreadsheets to minimize Excel frustration.
In the next video I’ll show you how to easily solve that non-linear system of equations in Excel. I’m excited to show this to you because once you learn it you’ll know how to do something in Excel that 90 percent of engineers haven’t discovered yet and once we’ve solved that system of equations, we’ll use advanced look up functions to find the correct hardware size for the design.
But first I want to know what you’re thinking. Leave a comment below with any questions and let me know how you want to use Excel for your engineering projects.
Thanks again for watching and I’ll talk to you later.