Download Excel Solver to solve the optimization problems and more Lecture notes Analytical Geometry in PDF only on Docsity!
Using Solver
Excel has two tools, Goal Seek and Solver, that can save a great deal of time with complex mathematics. From a practical point of view the simple tool Goal Seek is redundant. It has limited scope and is far outpaced by Solver. So why is it there? Simply because, being easier to use, it is less intimidating for the mathematically challenged. So we shall spend a brief time on it.
Solver, which is leased by Microsoft from Frontline Systems Inc., was developed primarily for solving optimization (maximum and minimum) problems. However, it can also be used to solve equations, and that is where we shall start. You may wish to visit www.solver.com to learn more about this product and its variations. The site also has a tutorial for using the Excel Solver, but it concentrates on optimization problems; we will do more with Solver.
In this chapter we will see examples where Solver is used (i) for equation solving, (ii) for curve fitting or regression analysis, and (iii) some simple optimization problems.
Solver needs to be installed on your computer and loaded into Excel. It is likely this happened when Excel was installed. To check, open the Data tab and look in the Analysis group for a Solver icon. Ifyou do not see it, use the Excel Help with the search word solver to get instructions on loading it. The Excel Help has nothing more about Solver, as Solver has its own Help facility.
Exercise 1: Goal Seek Suppose you have an equation such as Exp(-x) - Sin(x) = a and
you know (perhaps from making a simple plot) that this has a
root such that a <=x <=1. You could set up a worksheet similar
to Figure 12.1 (please ignore the Goal Seek dialogs for now), and by altering the value in AS and watching BS you could find whatvalue ofx makes the function zero. Think for a moment of what strategy you would adopt. You could confirm that there
was a root within (0,1) by making AS first a then 1 and
observing that f(x) changes sign. Next, you might next try the midpoint O.S and then 0.6. As the sign and magnitude of BS change, you would modify the direction and amount by which
212 A Guide to Microsoft Excel 2007 for Scientists and Engineers
you altered AS until BS was nearly a-or you got tired of the game! Well, Goal Seek wo rks the same way. Let us see Goal Seek at work.
Figure 12. (a) On Sheetl of a new workbook, enter what you see in Figure 12.1 without the text box. The formula in BS is =EXP(-A5)- 5IN(A5).
Note that the To Value must be a number; it cannot be a cell reference. 50 if you want D5 to equal D6, then you will need a cell with =D5-D6, and this will be your SetCellwith oas the To Value.
(b) Use the command Data / Data Tools / What-IfAnalysis to open the Goal Seek dialog.
(c) Our formula is in B5, so this is the Set Cell. We want to make this 0, so that is what we type in the To Value box, The variable is the value in A5, so this is the By Changing Cell. When these have been entered, click the OK button.
(d) Goal Seek now displays its Status dialog giving you the option to either accept what it has found or cancel the operation. Click OK.
(e) Repeat steps (c) and (d) using different starting values (say 0,1, and 0.5). Note howyouhave to reenter the problem each time you call up Goal Seek.
Note that the results vary slightly. Goal Seek quits when it has made a certain number of trials (iterations), when a certain time period has passed, or when two answers are within a certain range of each other (convergence limit). There is no way of changing these settings.
(f) If your starting value is 2, Goal Seek will find another root. Make a quick plot and see if you understand why.
(g) Save the workbook as Chap12.xlsx.
214 A Guide to Microsoft Excel 2007 for Scientists and Engineers
Note that Solver has its own Help feature.
Solving Equations
with Constraints
THIS IS IMPORTANT: Many Excel texts do not use Solver correctly. It is not necessary to have a Set Target Cell. We can find roots of equations using Changing Cells and Constraints. This method can make the problem easier to set up and will often give better results.
for this problem, but you may wish to use Solver's Help to learn a little about the first few optional settings.
(g) Save the workbook.
Figure 12.
If,in the last Exercise, you did look at Solver's Help and read about the Precision setting, you saw that it said: Controls the precision of solutions by using the number you enter to determine whether the value of a constraint cell meets a target or satisfies a lower or upper bound. This might sound irrelevant to the problem, but it is not.
With a starting value of 1 in AS and the default value of Precision at 0.000001 (that's five zeros after the decimal), Solver's answer made B5 avalue of-2.1E-08 (your result could differ slightly). But when Precision is set to 0.00000000001 (ten zeros after the decimal), the value was 1.8E-12. Higher precision leads to an answer closer to zero.
This is because: when the Value O[model is used in Solver, it is treated as a constraint problem. Indeed, to solve the last problem, we could have cleared the Set Target Cell and enter a constraint in the Form $B$5 = O.
This becomes very important when you want more than one cell to take on a certain value. Suppose your model requires all cells in D1:D10 to become zero. Ifwe insist on using the Value afmethod, we need a single cell, and so write =SUMSQ(Dl:DlO) in Dll and use it as the Set Target Cell. Of course, =SUM(D1:D10) would not work since cells with positive and negative values could sum to
Exercise 3: Finding
Multiple Roots
Using Solver 215
zero. A far better way is to use the constraint setting ofD1:D10 = O. This is the method we use in the next exercise.
In solving the simple cubic x3+8x^ 2-9x-72=O,^ we will see how the constraints method works and gives superior results. The graph of this function (or simple factorization) shows the roots to be 3, -3, and -8. Solver, like Goal Seek, homes in on the root that is closest to the initial value (sometimes called the guess) without passing through a minimum or maximum of the function. So we will be careful with our starting values. In more complex cases, one needs to experiment to find the multiple roots.
12 Figure 12. (a) On Sheet2 ofChap12.xlsx, copy from Figure 12.4 the text and values in columns A and B.
There is an analytical method for solving cubic equations. This is shown in the workbook CubicEqn.xlsx on the companion website.
(b) The formula in CS is =B5"'3+8B5"'2-9B5-72, and this is copied down to C7. In C9 we have =SUMSQ(C5:C7). The entries in E:F are for the next exercise.
We begin by using the "traditional" method of having a cell with a SUMSQ formula as our target cell.
(c) Use Solver as in Exercise 2 with The Set Target as C9, Value Of selected and set to 0, and By Changing Cells as B5:B7. Click the Solve button in the top right corner. We get results that are reasonably close to the known roots.
Now we will solve the same problem with no target cell but with a constraint.
(d) Reenter 4, -4, and -8 in BS:B7. Open Solver and clear the Set Target box.
Exercise 5: Systems of
Nonlinear Equations
Using Solver 217
(c) Set Solver up to use the constraint and no target cell. With FS as the active cell, open Solver's Option dialog and click on Save Model. Solver highlights a S-by-1 range; click OK. Mark these with borders/and color fills.
(d) Nowyou can switch from one model to the next using Options / Load Model and selecting with appropriate block of cells.
(e) Save the workbook
In Chapter 4 we saw the use of Excel's matrix functions to solve systems of linear equations. Figures 12.5 and 12.6 show a worksheet and Solver dialog used to solve a system of nonlinear equations. The starting values for x and y were both 1. The answers are not perfect; they should be integer 2 and 3, but the precision of the method is generally acceptable for real-world problems.
Figure 12.
Figure 12.
218 A Guide to Microsoft Exce/2007 for Scientists and Engineers
Curve Fitting with
Solver
In Chapter 7 we used various Excel functions (such as SLOPE, INTERCEPT, LINEST, and LOGEST) to fit experimental data to various mathematical models (linear, polynomial, exponential, etc.). We saw that the theory behind these fitting functions was based on the principle of minimizing the sum of the squares of the residuals. Solver was designed to perform maximization and minimization operations, and so lends itself to curve-fitting problems.
To demonstrate this method, we will do a simple linear fit with some test data taken from the NISTwebsite (www.nist.gov). NIST offers many data sets, together with their fitting parameters to enable others to test their regression programs. We shall use the Norris data set. Figure 12.7 shows a worksheet used to fit the
Norris data to y = mx + b. The Norris data set is shown in Figure
12.8.
Figure 12.
The heading s, y, and yfit in the data set were used to name the columns of data. Cells BS and CS were named as m and b, respectively. The formula in each cell inyfit is =mx+b. Note how Excel lets us use x to refer to just a single cell in this formula. The formula In B3 is =SUMXMY2(y,yfit). This function conveniently generates the sum of the squares of the residuals. Cells B6:C6have the formula =LINEST(y,x), while B7:C7 are copies of the values from the NIST website.
With initial values of m and b as 1, the Solver model used The Set Target is C5, with Min box selected and By Changing Cells as B5:C5. While the LINEST results are much closer to the accepted NIST values, the Solver answers are quite acceptable.
220 A Guide to Microsoft Exce/2007 for Scientists and Engineers
Exercise 6: Gaussian
Curve Fit
Having demonstrated that this is a viable method of performing regression analysis, we will use it in some more challenging examples
Figure 12.9 shows, inA10:B41, some experimental data that is to be fitted to a Gaussian curve. The function is given by:
( (^ ) Yi = hexp - -(J-Xi^ -^ JL^2 J -b
where: Yi =^ the^ predicted^ value h = the peak height above the baseline Xi =^ the^ value of^ the^ independent^ variable fl =^ the^ position of^ the^ maximum a = the standard deviation and b = the baseline offset
(a) On Sheet5 of Chap12 .xlsx, start a worksheet similar to that in Figure 12.9. Begin by entering all the text and values except the values in C4.
(b) In C4:C7 use the same values as inA4:A7. Use B4:B7 to name the cells in C4:C7. We are going to vary the C4:C7 cells with Solver but will kept the A4:A7 values to remind us of our starting values.
(c) The formula in en is =h*EXP(-(((All-mu)/sig)"2))+base.
There may appear to be an extra pair of parentheses in this, but that is not the case; we need to allow for the fact that the negation operator has the highest priority.
(d) Construct a chart of the data in A10:C41. This will resemble the chart in Figure 12.10 where the markers are they-values and the line the yfit-values.
You may have been wondering where the starting values for the h. tnu, and sig parameters came from. The chart will answer this question. The height appears to be about 1600; the midpoint seems to be in the range 0.25 and 0.26 so we use 0.255 for mu. The starting value for sig is found by experimentation. Try 1 in C6 and see the effect onyfit. Now try 0.5 and again see the effect onyfit. You will find that 0.005 gets yfit to more or less fitthe y-values. The tails of the curve are notfar from zero, so a starting value of
ofor b would be appropriate. So now we have reasonable starting
Using Solver 221
parameters.
(e) To get ready for Solver we need a target cell holding the sum of the squares of the residuals. In C8 enter the formula =SUMXMY2(Bll:B41,Cll:C41).
(f) Use Solver to complete the task. The target cell is C8, which we wish to minimize by changing C4:C7. The resulting values are shown in Figure 12.9, while Figure 12.10 shows before and after fitting plots.
(g) Save the workbook.
Before After 1800 1600 1400 1200 1000 800 600 400 200
1800 1600 1400 1200 1000 800 600 400 200 0.230 0.240 0.250 0.260 0.270 0. Figure 12.
0.230 0.240 0.250 0.260 0.270 0.
Exercise 7: A
Minimization Problem
Scenario: An open-top tank is to be made from a sheet of metal by bending and welding (Figure 12.11). The specifications are that the volume is to be 1.0 m' using the minimum sheet area. You are to find the dimensions a and b.
a
a
Figure 12.
The worksheet to solve this problem, together with the Solver dialog, are shown in Figure 12.12.
Figure 12.
Using Solver 223
plus $1 or $1.50 while Gamma has 400 tons at $8 plus $5 or $3 for shipping. Develop a business plan for Sandbagger's operation today.
This is a typical Solver optimization problem. There are three groups of data to be processed; (i) the constants, (ii) the independent variables (called the decision variables), and (iii) the dependent variables leading to a problem objective function subjectto some constraints. Our constants relate to the two plants and the three suppliers. The independent variables are how much sand from each supplier goes to each plant The dependent variables are the expenses and income, with the profit being the objective function. The constraints are the finite amount each supplier has and the processing limit of each plant
With this in mind we plana worksheet with different areas for the three groups of data. The constraints are placed in the Solver dialog. Figure 12.13 shows our final worksheet
(a) On Sheet 8 of Chap12.xlsx enter all the text shown in the figure. Enter the values shown in columns Band C.
224 A Guide to Microsoft Excel 2007 for Scientists and Engineers
(b) Enter the values of 50 into G6:H8 as our starting values for Solver to work with. These are summed in row 9 and column I with formulas such as =5UM(G6:G8).
(c) In G13 enter =G6*($C14+B20) and copy this across and down to fill G13:H16. Sum these values in column I and row 16. Clearly, 117 gives the total of all expenses.
(d) Enter in G19 =I9*B5 (total income) and in G21 =G19-I (profit). This last item is our objective function.
Figure 12.
(e) All that remains is to run Solver; the settings are shown in Figure 12.14. The maximized profit comes out as $15, using all available supplies. Save the workbook.
TK Solver" One^ way^ to^ test^ results from a^ computer^ program^ is to^ set^ up^ the
same problem in two applications. Some programmers use Excel to test results from a C# program. For the test to be valid, you must totally rethink the problem. It is no good just programming the same algorithm into the second application. We want to test both the algorithm and its implimentation in the computer application.
A number of the problems in this book have been reworked in TK Solver. For many problems, this can be a delightfully easy application to use: you enter rules on one sheet and variables on the other. Then you tell TK Solver to find the unknown variable. Visitthe author's we bsite at people.stfx.cajbliengmejTKSolver to locate the files and a link for a free trial of TK Solver. This application, which is used in many Engineering schools, can also be interfaced with Excel to expand its capabilities.
226 A Guide to Microsoft Exce/2007 for Scientists and Engineers
3y=18-2x^2
Area A
0.5 1.5 2.5 3.
2V. G. Jensen and G. V. Jeffreys, Mathematical Methods in Chemical Engineering 2 nd^ ed., Academic Press, San Diego, 1977, (page 570).
- *Acompany has four sources of crude oil." Crude from each source can produce specific amounts of various products. Thus from column B in Figure 12.16 we see that crude A makes 60% gasoline, 20% heating oil, and so on. The company has a market for certain amounts of each product in a week (column G) and fixed supplies from each source (row 10). The profit per barrel is given in row 11. How many barrels of each type should be processed to maximize the profit?
Figure 12.
- *For a change of pace, solve this magazine puzzle. Which three-digit number, when you divide it by the sum of its digits, gives you the sum of its digits plus one?
- The Langmuir equation relates the amount of gas (5) absorbed on a surface to the pressure (p) of the gas.
S = KS rnax
l+Kp
Fit the data in the following table to find K and Smax'
P 5
Using Solver 227
79.34 162.31 253. 29.86 38.45 43.
t (0e)
11 (expt)
- InProblem 15 of Chapter 9, this equation was used to find the surface area of a cylinder with a conical base.
S = ---;:-^ 2V^ + J[ r 2(^ esc e - "3^2 cot e )
We can show by calculus that S is a minimum when the angle of the cone is given by 8 =cosĀ·^1 (2/ 3). Use Solver to confirm that we did the differentiation correctly. How close is Solver's value to the expected one? Can you improve on this?
- Sutherland's equations can be used to derive the dynamic viscosity of an ideal gas as a function of temperature:
- Ta+C(~J% 17 - 170 T + C Ta
where 11 is the viscosity (Pa-s) at temperature T, 110 is the viscosity, T is the input temperature in Kelvin, To is the reference temperature, and Cis Sutherland's constant for the specified gas. The following table lists some measured viscosity values for air. Given that for air, 110 = 18.27 X 10.^6 Pa-s at 291.15K, find C for air.
10 20 30 40 50 60 70 80 90 100 17.87 18.37 18.86 19.34 19.82 20.29 20.75 21.21 21.66 22.
- Refer to Problem 9 in Chapter 2. Make a new worksheet beginning with something similar Figure 12.17. Use Solver to find the n values that maximize the profit
Figure 12.
Do not use the UDF you may have coded in Problem 7 of Chapter 9 but compute the ml values with an Excel function.
