[thrive_leads id=’2430′]
Welcome to the second video in this three-part Excel training for engineers. In this video, we’re going to look at how to handle complex calculations in Excel with ease. Thanks again for watching the first video and giving me your questions and feedback. In that video, I showed you how to use name ranges and calculations instead of cell references to make formulas less confusing and more readable. I also showed you how you can convert units right in Excel using the convert function without having to look up conversion factors from other sources. And if you didn’t get a chance to watch that video, go back and check it out right now before you watch the rest of this one.
In this video, I’m going to show you how to do something in Excel that only a small percentage of engineers know how to do, so let’s get into it. Okay, with all these preliminary calculations out of the way, the next thing we need to do is calculate the required total area for the bolts and to do that, we need to solve this system of nonlinear equations. (Hint: Here’s a great way to document equations in your spreadsheets.) Now there’s five equations and five unknowns here. And the reason I say that it’s a system of nonlinear equations is really just because the first two equations are nonlinear and that’s because that area is in the denominator on the right hand side of the equation. The remaining three equations are linear equations but because of those first two are nonlinear, that makes this whole system nonlinear and we can just solve it using traditional linear algebra methods.
So like I said, that area is showing up in the first two equations in this system of equations and to solve that system of equations for area, we’re going to use a tool that’s built into Excel called Goal Seek. Now Goal Seek can be found if you go up to the data tab and then in the forecast selection, open the what if analysis drop down and select Goal Seek. Goal Seek has three inputs, the first one in the set cell. That’s the value that you want to change to a certain value which you specify then in two value and then the last entry is the by changing cell and this is a cell that you’re going to modify in order to get the end result that you want in the set cell.
Now I’ve kind of flow charted here how we’re going to use Goal Seek to solve this system of equations. The first thing we’re going to do is we’re going to take a guess at the total area of the bolts that’s holding the lid on the pressure vessel. We’re going to use that guess then to calculate sigma max and sigma min, the max and min stresses in the bolts. From there, we can calculate the mean and alternating stress in the bolts. And once we’ve done the mean and alternating stress, we can calculate the equivalent stress.
Then we’re going to do a check to see if the equivalent stress is equal to endurance strength divided by the safety factor. If those two quantities are not equal, then we’re going to use Goal Seek to go back and keep guessing at the area until they ultimately are equal and at which point we’ll have the area value that we’re looking for and we can end the Goal Seek routine.
Now all this happens automatically within Excel without us having to do all these iterations, the software just does it for us. And if it’s not clear exactly how this works, just bear with me as we go through this example, you’re going to see exactly how it’s done.
Okay, so back in our spreadsheet again, the first thing we need to do is enter a space where we can put the guess value for the area and this is going to be the total area. So I’ll go ahead and copy this formatting down and we’re actually going to need to create six more entries here. We’re going to create a guess value for the area and then we’re going to enter our five equations so I’ll just go ahead and enter or create six new rows here in our table of calculations. The first one again like I said is total bolt area and this is just going to be a guess for now. So we’ll just enter a value of five square inches. I’ll call this A_total.
[thrive_leads id=’2430′]
The next line we need to enter in is the calculation for the maximum stress. And in fact while I’m at it, I’m just going to go ahead and enter all these descriptions in, so we’ll do minimum stress as well as mean stress, alternating stress and the bolt equivalent stress. Okay and I’ll also create the named ranges for those I did total area already. So we’ll do sigma_max, we’ll do sigma_min, sigma_mean, sigma_alt and finally sigma_eq_2 to distinguish it from the bolt equivalent stress that we calculated in this row already.
Okay now let’s go ahead and enter in that calculation for the maximum stress. As I showed you before, the calculation is going to be 1.5 times the max force. That’s because we’re going to assume that the preload is equal to 150% of the max force plus the component of the alternating force that’s carried by the bolts and we decided we were just going to assume that that was 2% of the max force. Okay and then that’s all divided by the total area to calculate the stress.
Next we can go ahead and calculate the minimum stress. That equation’s very similar. It is going to be 1.5 times F max plus .02 for 2% times the minimum force, again divided by the total area. Now the mean stress is then calculated as sigma_max plus sigma_min divided by 2. The alternating stress is where we include the stress concentration factor. So that’s going to equal K_thread divided by 2 times sigma_max minus sigma_min. And then finally the bolt equivalent stress is going to equal sigma_alt for the alternating stress plus the endurance strength SE divided by the ultimate strength times the mean stress sigma_mean. Okay and that gives us our bolt equivalent stress and we’ll put some units on these. PSI for all.
So now what we need to do is we need to find the value of the total bolt area that’s going to set this bolt equivalent stress calculated here equal to this bolt equivalent stress we’ve calculated using these other equations. But Goal Seek can’t tell when two cells are equal to one another so what we need to do is actually subtract the two values and set a goal of obtaining a value of zero. So we’ll just create a row here called equivalent stress difference and the calculation there will be sigma_EQ_2 minus sigma_EQ and the units on that will be PSI. Now of course that’ll be PSI.
Now of course we have a difference here and this value is not equal to zero and that’s because our total bolt area has not been chosen appropriately to make the equations work out. Now we could probably guess at it and we could eventually get there but we cold also use Goal Seek as well. So before we do that, I’m going to increase the number of decimals here so that we can see how the value is actually changing. And we’ll go ahead to our data tab, select what-if analysis and then Goal Seek. Okay, we want to set the cell right here, this equivalent stress difference that we calculated to a value of zero and we want to do that by changing our guess value for the total bolt area.
Okay and that’s all there is to it. We just have to enter those three things into those boxes in the Goal Seek window and then we just click okay and Goal Seek iterates until it gets this equivalent stress difference equal to zero within some tolerance and we find that the total bolt area required for this design to meet the design requirements of infinite life with a safety factor of three is 4.46 inches. Now we don’t need to have all those zeroes on there so we’ll just reduce it down to three, change the field so it matches everything else.
So the next thing we can do is take the total bolt area and divide it by the number of bolts to figure out the area per bolt that’s required so that we can actually size the individual fasteners. So we’ll just call this area per bolt and this is going to equal A_total divided by N_bolts and of course that is in units of inches squared. Well as you know, bolts are not specified by their area, their specified by a nominal diameter so we need to take this area that we’ve calculated and determine what the nominal diameter is with the bolt that we’re going to need to specify for this design.
So I’ll go ahead and create the third section of this spreadsheet. We’ll call it results or just result, ’cause there’s really only one value that we’re concerned about and we’ll call this nominal diameter. All right, we’ll scoot over here so we got a little bit more space to work with. We’ll expand this. The value that we’re going to return is in inches. So what we really need to do here is we need to find a bolt diameter with a tinsel stress area that exceeds the calculated requirement. The bolt size is not going to be correct unless it gives us an area that is greater than the area per bolt. We’ve calculated in this cell.
Now there’s no mathematical relationship between the tinsel stress area and the nominal diameter, we get that from looking in tables of data. So what I’ve already done is I’ve already pulled some bolt data into this third sheet in this workbook. It contains several different columns. The two columns that we’re interested in specifically are the nominal major diameter and the tinsel stress area. And I’ve already gone ahead and created some named ranges for those columns that contain the diameter values in the tinsel stress areas and we’re going to use that in a lookup function to determine the proper bolt diameter for this design.
Now you might be familiar with a V lookup function but in this case, we can’t use a V lookup because in order to use a V lookup, the value that you’re looking for has to be in the far left column of the table. But in this case, the data for diameter is in the second column and that’s the value that we want to return from that column and the array that we’re going to look through and try to find the lookup value is actually in this fourth column right here in the tinsel stress area.
So rather than using a V lookup function, we’re going to do more of an advanced lookup using index and match functions and in order to do that, I’ve already set this column up to be in descending order so the largest tinsel stress area is on top and the areas decrease as we go down the column here.
So let’s go back to our spreadsheet and set up that calculation. The first function that we’re going to use in this calculation is the index function. An index as it says here in the description, returns a value or reference of the cell at the intersection of a particular row and column in a given range. Okay, so what does all that mean? What it means is that we’re going to input an array and we’re going to specify the row number in that array from which we want to pull a value. Okay, so it looks like this. We want to return to diameter. So the array that we’re going to look in is diameter. Now we already specified in that table of bolt data on the other sheet. Now to get the row number, we’re going to use another function called Match. And for this, we need to specify the lookup value that we’re looking for, which is this area right here. The lookup array which is that array of tinsel stress areas and the final argument that we need to enter is the match type.
We can choose one for less than, which means it’s going to find the largest value that is less than or equal to the lookup value, zero for exact match, which isn’t going to work for us because the area that we calculate isn’t necessarily going to correspond to a nominal diameter or we can use an argument of minus one which is going to find the smallest value that is greater than or equal to lookup value. So what this is going to actually do is it’s going to look in the array for this area that we’ve calculated in cell G19 and it is going to find the next largest area, find the position in the array of that next largest area, return that value into the index function and give us the matching diameter.
So I’ll add the two parentheses here and hit enter, increase the number of decimals and we get a nominal diameter of 1.25 inches. Make some of these columns a little narrower so we can see everything on one screen. Now that’s a really large bolt size and of course that’s happening because the number of fasteners that we’ve chosen. But we can look at where that value comes from if we do this manually. We can look at the area per bolt, go here to our bolt data tab, look for the smallest value of tinsel stress area that’s greater than that .74 that was calculated and that just happens to be this value right here, 1.073, the diameter that corresponds to that is 1.25 and we see that’s exactly what’s been returned to our spreadsheet in our results section.
So that’s how you can use Goal Seek to solve systems of equations in Excel and index and match to do advanced look-ups from tables of data. In the final video, I’ll show you how to use automation in Excel to avoid potential errors and mistakes in a spreadsheet like this. Thanks again for watching and I’ll talk to you later.
[thrive_leads id=’2430′]
