Excel ® solver

Harris, D. C. Nonlinear Least-Squares Curve Litting with Microsoft Excel Solver, /. Chem. Educ. 1998, 75, 119-121. 

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. 

The Quattro Pro Solver. The same team that packaged and developed the Excel Solver also interfaced the same NLP engine (GRG2) to the Quattro Pro spreadsheet. Solver operation and problem specification mechanisms are similar to those for Excel. 

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. 

T1 PROCESS MODEL SOLVED BY THE EXCEL SOLVER (BRANCH AND BOUND)... 

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

An evolutionary algorithm is included in the current release of Frontline Systems Premium Excel Solver (for current information, see www.frontsys.com). It is invoked by choosing Standard Evolutionary from the Solver dropdown list in the Solver Parameters Dialog Box. The other nonlinear solver is Standard GRG Nonlinear, which is the GRG2 solver described in Section 8.7. As discussed there, GRG2 is a gradient-based local solver, which will find the nearest local solution to its starting point. The evolutionary solver is much less likely to stop at a local minimum, as we illustrate shortly. 

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... 

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... 

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. 

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

---