EN3: Introduction to Engineering and Statics
Division of Engineering
Brown University
Tutorial for the Excel structural analysis spreadsheets
This
tutorial will show you how to use the EXCEL
energy minimization spreadsheet to analyze deformation and forces in a pin
jointed structure.
We’ll use the 2D spreadsheet to keep things simple – the 3D version works in exactly the same way, so once you’ve mastered this one you can easily upgrade to the more realistic version.
We will start by calculating the deflections and member forces in the simple 2D pin jointed frame shown below. All dimensions are shown in meters, and the members have stiffness 10000N/m.
We need to give the spreadsheet all the information it needs to analyze the structure. Then, we’ll run solver; and finally look at the results. We will work through the following steps – you can click on each link to go directly to the section you are interested in.
4. Displacements at constrained joints
7. Analyzing a statically indeterminate structure
8. How can you use the spreadsheet to tell if a structure is indeterminate?
Start by downloading the blank 2D structural analysis spreadsheet, available here. (to download the sheet, right-click on the link and select Save As, then enter an appropriate file name).
Entering the joint coordinates
Joint coordinates are specified by typing in x,y coordinates in the green cells of the Joints table. You need to start with joint 1 and then work your way down the table. Just leave blank any joints that don’t exist. The coordinates for our sample structure are shown below. Enter this data into your copy of the spreadsheet.
Next, we specify the forces acting at all the joints. To do this, we enter the and components of force acting on each joint. If there are no forces acting on a joint, we just leave the cell blank. So, for our example, we just need to enter the forces acting at joint (3). Note that we don’t need to worry about the reaction forces acting at the constraints – those are taken care of later.
Here’s how our spreadsheet looks after specifying the forces
Entering the member stiffnesses
Next, we specify the stiffness of each member. If you scroll across the spreadsheet, you will see a table with rows and columns indexed by joint number. You need to enter the stiffness of each member in the green cells. For example, if there’s a member connecting joints 1 and 2, you enter its stiffness in the cell at row 1, column 2 in the table. If there’s a member connecting joints 2 and 3, you put its stiffness in row 2, column 3, and so on. The figure below shows what needs to be set up for our example structure – remember that k=10000 N/m for each member.
Note that we only need to fill in the upper half of the table – the spreadsheet knows that if there’s a member connecting joint 1 to 2 there must be one connecting joint 2 to 1 as well.
Entering constrained displacements
The next item on our list of chores is to specify constrained displacements. The values of any known displacements must be entered into the pink cells in the Joints table. Usually, we know that some displacement components at constrained joints have to be zero, because the constraint prevents the joint from moving. Occasionally, you might want to calculate the forces you need to move the structure in some way.
For our example, we know that
1. at joint 1
2. at joint 2.
So we enter these values as shown below.
You should make sure that the cells for all unconstrained displacements are blank.
Finally, we can check and make sure that the structure looks the way we expect it to. Look down at the bottom of the spreadsheet and click the tab marked ‘Graphics’ as shown below
You should then see a picture showing the shape of the undeformed structure, as shown below.
Everything looks fine – we can go ahead and get a solution…
Finally, we’re ready to do the calculation. We need to set up solver as follows.
The objective function is the total potential energy – it’s in the yellow cell with index $E$2 near the top of the spreadsheet.
The variables are the unknown displacement components. Select these cells in Solver’s variable box in the usual way. The solver menu should look like this
Finally, it’s worth adjusting Solvers `Options’ to get the best possible solution. To do this, click on the Options button, then make sure that the Central Derivatives button is checked as shown below
Finally, hit OK on the Solver Options menu, then hit Solve on the Solver Parameters menu.
If everything worked as advertised, you should now have a solution to the example problem. Here is a brief summary of the information in the spreadsheet
1. The joint displacements are recorded in the Joints Table. These numbers usually aren’t of much interest to us – it’s more helpful to look at the shape of the structure. But if you need to know them, they are there for your enjoyment.
2. The member forces are recorded near the bottom of the spreadsheet, just under the member stiffness table. The solution to our example problem is shown below. Notice that the spreadsheet highlights cells that contain members in blue. In addition, note that two cells are highlighted in red. These are the members with the biggest internal forces. The spreadsheet finds the biggest tensile (positive) force, as well as the biggest compressive (negative) force. In our structure none of the members are in compression.
3. If you scroll the spreadsheet slightly left, you’ll see a box with some useful info in it.
4. We’ll see later that just above this info box, there’s an area where the spreadsheet warns you if it thinks there’s something wrong with your structure. We’ll explore this in a moment. For now, we note that there are no problems, so the solution should be OK.
5. Finally, if you click the Graphics tab again, the graph will now show you the deformed shape of the structure. The joint displacements are very small for our example, so the deformed shape looks exactly the same as the undeformed shape. But if you were to reduce the stiffness of all the members to k=1000 N/m, you’d see a significant shape change. Try this – but don’t forget to re run solver after you change the member stiffness! You should see that the deformed structure looks like the picture shown below. If you’re curious, try decreasing the member stiffness even more – try k=500 N/m or k=200 N/m. You’ll see big deflections on your graph, and will also see a warning message appear on the `problem definition and solutions’ sheet. The spreadsheet will warn you if any member stretches by more than 10% of its initial length – with such large extensions your structure is likely to have serious problems, unless it’s made of rubber!
Spreadsheet behavior for a statically indeterminate structure
OK, we’ve seen how the spreadsheet works for a simple
statically determinate structure. Now
let’s check out what happens if we make the structure statically indeterminate.
No problem… We just add another member (2-4) to the structure.
You should be able to convince yourself that Maxwell’s rule correctly indicates that this structure is statically indeterminate.
Set up this structure in the spreadsheet, giving all the members a stiffness of k=1000 N/m
Then, run solver. Find the forces in the members, and look at the shape of the structure. You should see the results shown below.
Note that the spreadsheet has absolutely no problem analyzing an indeterminate structure. In fact, you can’t even tell that the structure is statically indeterminate at all.
How can you use the spreadsheet to tell if a structure is statically indeterminate?
So how do we know if the structure is indeterminate? Easy. We can use one of three methods.
1. If you remove one member of a statically determinate structure, it will turn into a mechanism. So, you can try an experiment to see what happens if you remove a member. If you remove a member and the structure collapses, it was probably determinate before you removed the member. If you remove a member and it stays up, it’s probably indeterminate. I say ‘probably’ because this test isn’t 100% fool proof – it depends a bit on how the structure is loaded and which member you choose to remove. But it will work more often than not.
2. The internal forces in a statically determinate structure are independent of the stiffness of the members (provided that the members are stiff enough that deflections are negligible). So, we can change the stiffness of a few (but not all) of the members, and see if the forces change. If they change, the structure is indeterminate. If not, your structure is determinate or is a mechanism.
3. If you make a small change in the length of one member in a statically indeterminate structure, you will induce forces in its members. So, you can see what happens to the structure when you change the length of one member. But you can’t do this in the example spreadsheet we’ve been using, because that version calculates all the member lengths for you, based on the coordinates of the joints. Instead, you must download the advanced version of the spreadsheet, which allows you to change the length of the members. Click here to download this version, and check it out. If you scroll way over to the right of the `Problem definition and solution’ sheet you’ll see a new table, which looks like this
If one of the members is slightly longer, or slightly
shorter, than the distance between the joints, it is said to be
‘incompatible.’ You can make a member
incompatible by entering the difference between the member length and the
distance between the joints in the table.
If the member is exactly the right size, leave the cell blank. If it’s too long, enter a positive
number. If the member is too short,
enter a negative number.
For example, in our example structure member (1-4) is meant to be 2m long. Suppose that we actually have a member that’s 2.05m long instead, and try to cram it in between joints 1 and 4. What happens?
Set this up in the advanced spreadsheet. You need to enter 0.05 in cell $AJ$7, because member 1-4 is 0.05m longer than it is supposed to be. Remove any forces acting on the structure (delete them). Then, run solver again.
The figures below show the results you should get, using a member stiffness of 1000 N/m.
The member forces are not zero, so the structure must be indeterminate. As an experiment, repeat this test for the structure shown below
This time, you should find that the structure changes its shape (check the Graphics page), but all the member forces are zero (or exceedingly small).
That tells you the structure is probably not statically indeterminate – but you have to check and see what happens when you lengthen every member in the structure in turn to be absolutely certain.
Note that this test doesn’t tell you that your structure is determinate – it could just as well be a mechanism. But it’s easy to detect whether your structure is a mechanism, as we will see below.
So, finally, let’s put in a mechanism. Set up the structure shown on the right in the spreadsheet. You can use either the basic or advanced spreadsheets, but if you use the advanced version remember to remove the incompatibility of member (1-4). Give each member a stiffness of 10000 N/m.
Run solver again and see what happens.
You should see
(1) A warning appears below the joint table indicating that the spreadsheet has detected large displacements at one or more joints. The spreadsheet checks for deflections that are greater than 10% of the member length.
(2) If you check the values of and in the joint table, you will see that they are indeed very large.
(3) If you check the `graphics’ sheet you will see how the structure has changed its shape in response to the loading. A close look will show you that member (2-3) has rotated around until it is parallel to the resultant force vector acting at joint 3, as indicated in the sketch below. Member (2-3) is now supporting the entire load – and indeed if you check the magnitude of the tension force in member (2-3), you’ll see it’s equal to the magnitude of the resultant force.
This experiment shows that our spreadsheet has no problem analyzing the behavior of a mechanism – and in fact it will tell you exactly what will happen if you accidentally design a structure that behaves like a mechanism and try to apply forces to it! This can save you a lot of potentially embarrassing mistakes. Surely worth the price of tuition!
That’s it! You’re now ready to solve any medium sized structural analysis problem. You could make the spreadsheet bigger, of course, - and you can probably work out how to do this by looking closely at the formulas on the calculation pages. The standard solver is limited to 200 unknowns, so you can’t handle more than 100 joints in 2D, or 66 joints in 3D.