Using Excel's Solver 0) Introduction: 1) Installing Solver: 2) Roots and Extrema of Equations: 3) Failures: 4) Cannon: 0) Introduction: Excel's Solver is a simple to use tool that can helps us find the "best solution" to various mathematical problems. Previously, we have often used a slider along with a mathematical model to allow us to easily vary some "input" to the model and see what the resulting output was. Using this output as feedback, we moved the slider manually to find a solution: an output that we wanted. For example, moving the angle slider to find what x value maximized some function of x; moving the angle slider to find what angle caused the cannon shell to travel through an x-y coordinate; moving the dossage slider to find a dosage that kept the drug concentration between an effective dose and and overdose. In this lecture we will look at how we can use Exel's solver sometimes to automate this process. 1) Installing Solver: To check if the Solver has been loaded into Excel, look on the Data tab, at the far right to see if there is an Analysis group that contains the "?->Solver" icon. If it is not there, you can install it as follows: 1. Click the Microsoft Office Button (upper left icon), and then click Excel Options (left of two bottom icons). 2. Click Add-Ins, and then in the Manage box (at the bottom), select Excel Add-ins and click Go. 3. In the Add-Ins available box, click (so it is checked) the Solver Add-in check box, and then click OK. If the Solver Add-in is not listed in the Add-Ins available box, click Browse to try to locate the add-in. If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it. At this point you should see the "?->Solver" in the Analysis group on the Data tab. Of course, you may also find it useful to put this icon on the QAT. 2) Roots and Extrema of Equations: The Function tab includes cell B1 into which we can put an x value, and cell B2 into which F(x) is computed. The other information on this worksheet allows us to zoom in on any parts of the graph. We can use this zoom feature to find for what x value F(x) is 0, or find the x for which F(x) is maximum between 0 and 3, or find the x for which F(x) is minimum between 3 and 6. We can use the Solver to automate all three of these tasks. A) To find the root -->Enter the value 0 in B1 (x=0 is is not a root: F(0) is not 0, but it is close -->Click the "?->Solver" in the Analysis group on the Data tab -->In the Solver Parameters pop-up window... -->Enter the Target Cell to be cell B2, the cell computing F(x) -->After "Equal To:" click the radio-button "Value of:" and enter 0 in the --> textbox that values "Value of:" -->In the textbox under "By Changing Cells:" enter the cell B1, the cell --> containing the value x from which F(x) is computing; in fact, because the --> name x (look at the Name Manager) refers to cell B1, we can type the name --> x into this box as well -->Click the Solve button Quickly, the pop-up window named Solver Results will appear. It will say "Solver found a solution. ALl constraints and optimality conditions are are satsified" In cell B1, you will find the solution (x = .1389099) and in cell B2 you wil see that F(.1389099) = 1.413 x 10 ^ -7 (about 11 ten-millionth, which is close to 0). We can either click OK immediately, to keep the Solver's solution in Cell B1, or we can click the "Restore Original Values" radio button and then click OK, to restore the original value stored in cell B1 (0) before we started the Solver. You can also use Trace X and Zoom In to find x (as we did in the lab with this problem) and will find a similar value. B) To find a maximum between 0 and 3 -->Enter the value 2 in B1 (x=2 is is not the maximum, but it is close -->Click the "?->Solver" in the Analysis group on the Data tab -->Note: if you reuse the solver, the information you last entered intially --> appears -->In the Solver Parameters pop-up window... -->Enter the Target Cell to be cell B2, the cell computing F(x) -->After "Equal To:" click the radio-button "Max:"; ignore the textbox --> "Value of:" -->In the textbox under "By Changing Cells:" enter the cell B1, the cell --> containing the value x from which F(x) is computing; in fact, because the --> name x (look at the Name Manager) refers to cell B1, we can type the name --> x into this box as well -->Click the Solve button Quickly, the pop-up window named Solver Results will appear. It will say "Solver found a solution. ALl constraints and optimality conditions are are satsified" In cell B1, you will find the solution (x = 1.6075830) and in cell B2 you wil see that F of this value is the maximum of the curve near 2. We can either click OK immediately, to keep the Solver's solution in Cell B1, or we can click the "Restore Original Values" radio button and then click OK, to restore the original value stored in cell B1 (0) before we started the Solver. You can also use Trace X and Zoom In to find x (as we did in the lab with this problem) and will find a similar value. C) To find a mainimum between 3 and 6 Solve this similarly to the maximum problem, choosing 4 as the original value in cell B1 and choosing the radio button "Min" after "Equal To:" 3) Failures: On the Function tab, the Solver uses an intial guess and the curve to try to find a solution. When solving for F(x) = 0, if our initial guess is 0, the Solver computes F(x) as a negative number (-.81) and realizes the slope of the curve at that point is positive, so it tries a bigger number ... until if finds the root. If we started x and a negative number, it would work too. But, if we started x at 2, Solver computes F(x) as a positive number (3.35) and realizes the slope of the curve at that point is negative, so it tries a bigger number,but that is the WRONG WAY. In fact, there is no root near 2 that can be reachable by following the sloope of the curve. Evenually, the Solver shows a pop-up window named Solver Results. It will say "Solver could not find a feasible solution" So, the Solver is powerful, but it is not magic. It works in many but not all situations. Likewise, it cannot find the minimum/maximum value starting at -2/6, becuase it follows the curve downward/upward to the left/right but there is no minimum/maximum there (the curve keeps getting more a more negative/positive). 4) Cannon: The Cannon tab includes cell B7, into which we can put an Angle (normally by adjusting the slider, but we can directly enter a value there too) and cell B10, into which we can put a start speed (ditto). Given these values, Excel computes the trajectory of the cannon shell and shows it plotted in the chart. We can use Solver to find the firing Angle for any start speed, and also find the minimum start speed (and its Angle) so that we can hit (or get as close as we can) to the target's X and Y coordinates. In both cases we will use cell H6 to determine how much we missed the target by. It contains the formula =ABS(LOOKUP(B13,B17:B801,C17:C801)-B14) Which looks up the Y value in the trajectory calculation (when the X value is TargetX: cell B13) and computes the distance (an absolute value) between the calculated Y value at this X value, and the desired TargetY value (B14). The smaller this value, the closer we are to hitting the target (whether above it or below it). If this value is 0, it means we hit the target exactly, at least according to the calculation. So, this cell calculates how good is a particular Angle and Start Speed. A) Given a Start Speed, solve for the Angle -->Enter the value 1400 in B10 and 450 in B7 (the RAW VALUE for the Angle) --> Note that cell B8 will become 45: a first approximation to the correct --> Angle -->Click the "?->Solver" in the Analysis group on the Data tab -->In the Solver Parameters pop-up window... -->Enter the Target Cell to be cell H6, the cell computing the Amount of Miss -->After "Equal To:" click the radio-button "Min:" and ignore the value in the --> textbox "Value of:" -->In the textbox under "By Changing Cells:" enter the cell B7, the cell --> containing the RAW VALUE of the Angle (do not use cell B8, which contains --> the REAL VALUE computed from the RAW VALUE of the Angle -->Click the Solve button This process can take a while because every time Solver changes the Angle, Excel must recompute the entire flight, examine the Amount of Miss, and then readjust the Angle. After a while (6 seconds on my fast computer) the pop-up window named Solver Results will appear. It will say "Solver found a solution. ALl constraints and optimality conditions are are satsified" In cell B7, you will find the RAW solution (225.58985), and in cell B8 you will find the REAL solution (22.558985 degrees), and in cell H6 you will see that the Amount of Miss is = 1.57 x 10 ^ -5. By adjusting the angles with the slider you will see that the right REAL Angle is between 22.5 and 22.6 A) Find the minimum Start Speed for which we can solve for the Angle -->Enter the value 1400 in B10 and 450 in B7 (the RAW VALUE for the Angle) --> Note that cell B8 will become 45: a first approximation to the correct --> Angle -->Click the "?->Solver" in the Analysis group on the Data tab -->In the Solver Parameters pop-up window... -->Enter the Target Cell to be cell B10, the cell computing Start Speed -->After "Equal To:" click the radio-button "Min:" and ignore the value in the --> textbox "Value of:" -->In the textbox under "By Changing Cells:" enter the cells B7,B10 the cells --> containing the RAW VALUES of the Angle and Start Speed -->Click Add to the right of the "Subject to the Constraints" window -->Enter H6 into the Cell Reference; enter <= in the next pull-down list, --> and enter 1 in the Constraint: textbox; click OK So, here we are trying to minimize B10 (Start Speed) subject to the constraint that whatever Start Speed and Angle we choose, the Amount of Miss is <= 1, by varying the Start Speed and Angle. -->Click the Solve button This process can take a very long time because every time Solver changes the Start Speed or the Angle, Excel must recompute the entire flight, examine the Amount of Miss, and then readjust the Angle or Start Speed. After a while (30 seconds on my fast computer) the pop-up window named Solver Results will appear. It will say "Solver found a solution. ALl constraints and optimality conditions are are satsified" In cell B7, you will find the RAW solution (421.74865), and in cell B8 you will find the REAL solution (42.174865), and in cell B10/B11, you will find the RAW/REAL solution (1033.7923), and in cell H6 you will see that the Amount of Miss is about 1. By adjusting the Start Speed slider down to 1033, you will see that you cannot adjust the to hit the target (cell H6 shows a large miss).