Excel Tutorial - Part II

Excel Tutorial - Part II Purpose: Learn to use the goal seek and equation solver tools in Excel. Importance: While doing research, you will encounter simple and complex problems alike. Sometimes you will have to solve a simple problem like the cantilevered beam problem that you solved in the first part of this tutorial but subject to some constraints. For example, what would you do if you wanted to limit the maximum stress in the beam to 400psi? Equation solver and goal seek are tools included with Excel that allow you to find a solution for such a problem very quickly. What to hand in: N/A Goals: After this activity, you will be able to: • Software: Analyze the cantilevered beam problem using equation solver and goal seek Microsoft Excel SECTION 1: INTRODUCTION TO GOAL SEEK: In the cantilevered beam problem you learned that changing the input values for the dimensions of the beam and the applied force automatically changes the outputs. Now the following question arises: what if you wanted to limit the maximum stress in the beam to 400psi by changing the thickness of your beam? One way to find an answer for your question is to manually change the value for the thickness of the beam and observe the output for the maximum stress. After some iteration you may find the right value for the thickness that limits your maximum stress to 400psi. An easier way to do this is by using Goal Seek. Goal seek does the iterations for you until it finds a suitable answer. You access Goal Seek by clicking on Tools->Goal Seek. Then you fill in the three boxes. 1. Click inside the first box and delete anything that is in there. Then click in the cell of your spreadsheet that contains the cell whose value you want to change. If your maximum stress in on cell B8, then click on (or type in) B8. 2. Move over to the second box. If you want your maximum stress to be 400psi, then type in 400. DO NOT TYPE IN UNITS! 3. Move to the third box. Here you specify which cell to change in order to obtain a maximum stress of 400psi. Let's assume you want to change the thickness of your beam and that the thickness is on cell B4. Then click on (or type in) B4. 4. Click on OK. 5. Goal Seek displays a message box saying that it found a solution. Click on OK. 6. Look at your spreadsheet and notice how the thickness of the beam changed. NOTE: In some instances/analyses, a solution may not be possible. As a result, Goal Seek may return an error message reporting that it could not find a solution. You should not run into this problem during the tutorial. SECTION 2: EQUATION SOLVER Now let's complicate things a little. Say now you wanted to limit the maximum stress in your beam to 400psi but now instead of playing with just one dimension you decide to change both the thickness and the width of the beam. Whenever you want to solve a problem involving multiple variables or constraints, you need to use the Equation Solver. You access the equation solver by clicking on Tools>Solver. If you can't find solver under the Tools menu, it is probably not activated. To activate, follow this procedure. 1. Click on Tools->Add-ins. A list should appear. 2. Find solver in this list, check the box to its left and click OK. Solver should now be installed. If this doesn't work either, ask for help. Using Solver is similar to using Goal Seek. You have to move to each box and click on (or type in) cell designations from the spreadsheet. For the example shown below, solver will set the maximum stress (cell D11) equal to 400psi by changing the thickness and the width of the beam. Solver also accepts constraints. Say you wanted to limit the width of the beam to a value smaller than 1 inch and the thickness to a value between 0.1 and 0.8 inches. You need to click on the Add button. Another dialog box opens. Fill in the appropriate values to add each constraint. First add $C$3 <= 1 and click on Add. Then add the other two. At the end, click on Cancel. Click on Solve. A small window appears saying that solver either found or did not find a solution. You will be asked if you want to keep the closest solution or return to your previous values. Click on Keep solver solution and take a look at how your inputs changed.