Microsoft Excel Solver

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

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. 

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

The Solver code is not written by Microsoft, but instead is a product of Frontline Systems Inc. (P.O. Box 4288, Incline Village, NV 89450, www.frontsys.com). Essentially the same code is used in Microsoft Excel Solver, in the Lotus 1-2-3 Solver, and in the Quattro Pro Solver. 

H NMR measurements were performed at 20°C using a Maran NMR spectrometer (Resonance Instruments, UK) operating at resonance frequency of 23 MHz. The second moment of the FIDs was calculated using Microsoft Excel Solver. 

The results of using Microsoft Excel Solver (one of many possible analytical tools) on the optimal solution are shown in Figure 2.7. The results show that the optimal cost to satisfy demands can be decreased to 600,000. The key to achieving this solution is to choose which warehouse supplies customer zone C2 and thus how the plant capacity will be used. 

In this case, to solve these problems (LP and IP), we will detail the use of the Microsoft Excel Solver tool, which allows us to solve both LP and IP problems. 

The response of the ECD is not linear over a large concentration range, and the curvature varies with the compound. Therefore, calibration curves have to be determined, one for each halocarbon. again each one with at least seven points. The calibration values can be fitted to polynomial equations e.g., Microsoft Excel Solver). The curve fitting should be done stepwise, first assuming a second order equation and then refining to higher orders if needed, until the deviations of standard points from the curve are within acceptable limits. 

A new tool for a lifetime distributions analysis of emissions of probes adsorbed onto heterogeneous surfaces was recently developed by our research group [18]. This new methodology allows for asymmetric distributions and uses pseudo-Voigt profiles (Gaussian-Lorentzian product) instead of pure Gaussian or Lorentzian distributions. A very simple and widely available tool for fitting has been used, the Microsoft Excel Solver. This is a convenient way to treat the emission decay... 

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

The Wegstein method is a secant method applied to g(x) = x - Fix). In Microsoft Excel, roots are found by using Goal Seek or Solver. Assign one cell to be x, put the equation for/(x) in another cell, and let Goal Seek or Solver find the value of x that makes the equation cell zero. In MATLAB, the process is similar except that a function (m-file) is defined and the command fzeroCf .xO) provides the solution x, starting from the initial guess xO. 

If/(x) has a simple closed-form expression, analytical methods yield an exact solution, a closed form expression for the optimal x, x. Iff(x) is more complex, for example, if it requires several steps to compute, then a numerical approach must be used. Software for nonlinear optimization is now so widely available that the numerical approach is almost always used. For example, the Solver in the Microsoft Excel spreadsheet solves linear and nonlinear optimization problems, and many FORTRAN and C optimizers are available as well. General optimization software is discussed in Section 8.9. 

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. 

GRG2. This code is presently the most widely distributed for the generalized reduced gradient and its operation is explained in Section 8.7. In addition to its use as a stand-alone system, it is the optimizer employed by the Solver optimization options within the spreadsheet programs Microsoft Excel, Novell s Quattro Pro, Lotus 1-2-3, and the GINO interactive solver. 

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

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

Using a solver technique (we have used the solver add-in in Microsoft Excel 6.0 ), one can calculate the 3 molecular descriptors (H-bond acidity, H-bond basicity and polarisability-dipolarity). Plass et al. 1122] published the molecular descriptors of tripeptide derivatives based on the above-described method. Although reasonably sensible data were obtained, the method has not yet been validated on a large number of... 

In the present model the pre-exponential factors and activation energies are optimised by non-linear regression using the Solver module on Microsoft Excel. The amounts of coke associated with CO2 evolution at Pi and P2 are also optimised, while a mass balance determines the coke quantity associated with CO at P5 and CO2 at P3. The oxygen partial... 

Spreadsheet Summary In the first three exercises in Chapter 5 of Applications of Microsoft Excel in Analytical Chemistry, we explore the solution to the types of equations found in chemical equilibria. A general purpose quadratic equation solver is developed and used for equilibrium problems. Then, Excel is used to find iterative solutions by successive approximations. Excel s Solver is next employed to solve quadratic, cubic, and quartic equations of the type encountered in equilibrium calculations. 

Spreadsheet Summary In Chapter 5 of Applications of Microsoft Excel in Analytical Chemistry, we explore the solubility of a salt in the presence of an electrolyte that changes the ionic strength of the solution. The solubility also changes the ionic strength. An iterative solution is first found, in which the solubility is determined by assuming that activity coefficients are unity. The ionic strength is then calculated and used to find the activity coefficients, which in turn are used to obtain a new solubility. The iteration process is continued until the results reach a steady value. Excel s Solver is then used to find the solubility directly from an equation containing all the variables. 

Spreadsheet Summary In the fmst exercise in Chapter 6 of Applications of Microsoft Excel in Analytical Chemistry, we explore the use of Excel s Solver to find the concentrations of Mg, OH, and H3O+ in. the Mg(OH)2 system of Example 11-5. Solver finds the concentrations from the mass-balance expression, the solubility product of Mg(OEl)2, and the ion product of water. Then Excel s built-in tool Goal Seek is used to solve a cubic equation for the same system. The final exercise in Chapter 6 uses Solver to find the solubility of calcium oxalate at a known pH (see Example 11-7) and when the pH is unknown (see Feature 11-1). 

Spreadsheet Summary The second exercise in Chapter 13 of Applications of Microsoft Excel in Analytical Chemistry involves enzyme catalysis. A linear transformation is made so that the Michaelis constant, K, and the maximum velocity, can be determined from a least-squares procedure. The nonlinear regression method is used with Excel s Solver to find these parameters by fitting them into the nonlinear Michaelis-Menten equation. 

CHEOPS obtains this setup file in XML format from ModKit-l-. Tool wrappers are started according to this XML file. The input files required for the modeling tools Aspen Plus and gPROMS are obtained from the model repository ROME. CHEOPS applies a sequential-modular simulation strategy implemented as a solver component because all tool wrappers are able to provide closed-form model representations. The iterative solution process invokes the model evaluation functionality of each model representation, which refers to the underljdng tool wrapper to invoke the native computation in the modeling tool the model originated from. Finally, the results of all stream variables are written to a Microsoft Excel table when the simulation has terminated. 

Because Solver is such a generally useful tool, it may be desirable to call it from inside a macro. This can be especially helpful in iterative procedures, where Solver must be called repeatedly. In the more recent versions of Excel this is indeed possible. It requires that you open your spreadsheet, select the VBA editor (with Alt + Fll), and use Tools => References. In the resulting References - VBAProject dialog box, under Available References, find and activate SOLVER.xls. To do so, click on Browse, select Files of type Microsoft Excel Files (. xls,. xla) and, in Look in , find where Solver is located. For example, you may have to double-click on SystemdiskfC ), Program Files, Microsoft Office, Office, Library, and Solver. In the File name window type Solver.xla, and Open. 

---