Using Excel solver

Using Excel solver to Optimize More Than One Parameter... 

To extract acid dissociation constants from an acid-base titration curve, we can construct a difference plot, or Bjerrum plot, which is a graph of the mean fraction of bound protons, H, versus pH. This mean fraction can be measured from the quantities of reagents that were mixed and the measured pH. The theoretical shape of the difference plot is an expression in terms of fractional compositions. Use Excel SOLVER to vary equilibrium constants to obtain the best fit of the theoretical curve to the measured points. This process minimizes the sum of squares [nH(measured) -nH( theoretical) 2. 

This chapter describes several applications of absorption and emission of electromagnetic radiation in chemical analysis. Another application—spectrophotometric titrations—was already covered in Section 7-3. We also use Excel SOLVER and spreadsheet matrix manipulations as powerful tools for numerical analysis. 

Problem 19-13 gives an alternate way to find K by using Excel solver. 

The absorbance of a mixture is the sum of absorbances of the individual components. At a minimum, you should be able to find the concentrations of two species in a mixture by writing and solving two simultaneous equations for absorbance at two wavelengths. This procedure is most accurate if the two absorption spectra have regions where they do not overlap very much. With a spreadsheet, you should be able to use matrix operations to solve n simultaneous Beer s law equations for n components in a solution, with measurements at n wavelengths. You should be able to use Excel SOLVER to decompose a spectrum into a sum of spectra of the components by minimizing the function (Aca c — Am)2. 

Use EXCEL solver in order to find the temperature that maximizes Cg... 

Using Excel Solver to Solve for the Quadratic Equation, Example 6.1 ... 

Using Excel Solver to solve the quadratic equation, p. 198 The systematic approach to equilibrium calculations mass balance and charge... 

Use Excel Solver to calculate the concentration, x, in Problem 3 above. (The problem requires use of the quadratic equation.)... 

The solubility product for AgCl is 1.0 X 10 Use Excel Solver to calculate the solubility. 

Let s use Excel Solver to do the calculation for this problem. Denoting the two concentrations X and Y, we will use it to solve for F, and then substitute this value in the appropriate formula for X to calculate the value of X. We write... 

Using Excel solver for solving simultaneous equations for mixture absorbances (Example 16.5) ... 

A concentration can only be positive, and so the negative value of x is not a solution You can use Excel Solver for solving quadratic equations. See Chapter 6. 

Let us solve the constrained optimization problem posed in expressions (4.7 through 4.10) earlier, using the parameters stated in Example 4.2. The Excel set-up for this is shown in Figure 4.1, and we use Excel Solver to find the optimal solution. It turns out that our solution validates the decision from Example 4.2 to ship in full truckload quantities, as the optimal solution to the problem is Q = 2973 and R = 150. 

This mixed integer program with 18 binary variables, 16 continuous variables, and 38 constraints was solved using Excel Solver. The optimal order allocation is given in Table 6.11. Both suppliers are used for purchases. The policy results in a total cost of 93,980. 

A non-linear regression analysis is employed using die Solver in Microsoft Excel spreadsheet to determine die values of and in die following examples. Example 1-5 (Chapter 1) involves the enzymatic reaction in the conversion of urea to ammonia and carbon dioxide and Example 11-1 deals with the interconversion of D-glyceraldehyde 3-Phosphate and dihydroxyacetone phosphate. The Solver (EXAMPLEll-l.xls and EXAMPLEll-3.xls) uses the Michaehs-Menten (MM) formula to compute v i- The residual sums of squares between Vg(,j, and v j is then calculated. Using guessed values of and the Solver uses a search optimization technique to determine MM parameters. The values of and in Example 11-1 are ... 

We may compare our graphical result with the result obtained from solving for X and y by nonhnear regression fitting of the experimental rate curves to the power law form of eq. (8). Carrying this out using the Excel Solver tool... 

Another type of widely used modeling system is the spreadsheet solver. Microsoft Excel contains a module called the Excel Solver, which allows the user to enter the decision variables, constraints, and objective of an optimization problem into the cells of a spreadsheet and then invoke an LP, MILP, or NLP solver. Other spreadsheets contain similar solvers. For examples using the Excel Solver, see Section 7.8, and Chapters 8 and 9. 

A TRANSPORTATION PROBLEM USING THE EXCEL SOLVER SPREADSHEET FORMULATION... 

The Excel Solver. Microsoft Excel, beginning with version 3.0 in 1991, incorporates an NLP solver that operates on the values and formulas of a spreadsheet model. Versions 4.0 and later include an LP solver and mixed-integer programming (MIP) capability for both linear and nonlinear problems. The user specifies a set of cell addresses to be independently adjusted (the decision variables), a set of formula cells whose values are to be constrained (the constraints), and a formula cell designated as the optimization objective. The solver uses the spreadsheet interpreter to evaluate the constraint and objective functions, and approximates derivatives, using finite differences. The NLP solution engine for the Excel Solver is GRG2 (see Section 8.7). 

For examples that use the Excel Solver, see Chapters 7,9, and 10. For a description of the design and use of the Excel Solver, see Fylstra, et al. (1998). An enhanced version of the Excel Solver, which can handle larger problems, is faster, and includes enhanced solvers is available from Frontline Systems—see www.frontsys.com. This website contains a wealth of information on spreadsheet optimization. 

When the gap is smaller than some fraction tol of the incumbent s objective value (the factor 1.0 ensures that the test makes sense when lb = 0). When lb = — oo, you will always satisfy Equation 9.1. A tol value of 10-4 would be a tight tolerance, 0.01 would be neither tight nor loose, and 0.03 or higher would be loose. The termination criterion used in the Microsoft Excel Solver has a default tol value of 0.05. 

As discussed in Section 9.2, the Excel Solver uses a BB algorithm to solve MILP problems. It uses the same method to solve MINLP problems. The only difference is that for MINLP problems the relaxed subproblems at the nodes of the BB tree are continuous variable NLPs and must be solved by an NLP method. The Excel Solver uses the GRG2 code to solve these NLPs. GRG2 implements a GRG algorithm, as described in Chapter 8. 

Using the Evolutionary Algorithm in the Premium Excel Solver... 

B, and E are also shown. Although B is reasonably near its optimal value, A and E are far from theirs. This performance is comparable to that of the evolutionary algorithm in the Extended Excel Solver, shown in Table 10.10. If the decision variables in this problem must be binary, however, then OPTQUEST finds the optimal solution, whose objective value is 767, in only 116 iterations. The evolutionary algorithm found this same optimal solution in one of two runs using 1000 iterations. 

In addition to the Premium Excel Solver and Optquest, there are many other software systems for constrained global optimization see Pinter (1996b), Horst and Pardalos (1995), and Pinter (1999) for further information. Perhaps the most widely used of these is LGO (Pinter, 1999), (Pinter, 2000), which is intended for smooth problems with continuous variables. It is available as an interactive development environment with a graphical user interface under Microsoft Windows, or as a callable library, which can be invoked from an application written by the user in... 

As an integral component of Microsoft Office, the spreadsheet program Excel is installed on many personal computers. Thus, a widespread basic expertise can be assumed. Although initially designed for business calculations and graphics, Excel is also extremely useful for scientific purposes. Its matrix capabilities, as well as the optimisation add-in solver, are not widely known but can often be applied in order to quickly resolve quite complex multivariate problems. We have used Excel 2002 but any other version will do equally well. 

The Excel Solver Add-In is a very powerful tool. We have already used it to solve systems of non-linear equation, see Chapters 3.3.3 Solving Complex... 

J. Chem. Ed. 1994, 71, 602 E. Howard and J. Cassidy, Analysis with Microelectrodes Using Microsoft Excel Solver, J. Chem. Ed. 2000, 77, 409. 

---