Spreadsheet Solver

A search is then made by varying x and xf simultaneously (e.g. using a spreadsheet solver) to solve the objective function (see Section 3.9) ... 

However, the most appropriate value of the rate constant for each model needs to be determined. This can be determined, for example, in a spreadsheet by setting up a function for R2 in the spreadsheet and then using the spreadsheet solver to minimize R2 by manipulating the value of kA. The results are summarized in Table 5.6. 

This does not agree with the specified duty of 2.865 x 106 W. To make the heat duty balanced requires the heat transfer area A to be adjusted by trial and error. At each value of A, the number of tubes and hc must be calculated. This can be readily done using a spreadsheet solver. The result is ... 

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. 

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. 

Note If you solve these equations, or any cubic, using a spreadsheet solver, only one root is reported. To see all three roots you must use one of the analytical methods, or a program such as Mathematica or Maple. 

The ubiquitousness of the personal computer and the increasing power of the program packages available for it have shifted the emphasis away from large, mainframe-based systems like PLATO. Instead, graphics are included in utihty programs such as equation solvers, spreadsheet processors, and word processors. 

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 calculation is iterative and can be conveniently carried out using a solver in a spreadsheet to satisfy the Equations 15.65 and 15.66 simultaneously. To reach the two variables simultaneously, the objective can be set up such that the difference between the... 

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. 

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

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

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. 

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. 

A few comments and observations are appropriate. The quality of the initial guesses for the free concentrations of the components is more critical than in the Matlab Newton-Raphson routine introduced previously. The main disadvantage of the Solver, however, is the fact that it can only be applied to one instance. It cannot be dragged around on the spreadsheet like most other functions of Excel. It means, for our present example, that for each solution the Solver needs to be set up individually, defining the Set Target... 

The spreadsheet in Figure 4-62 is taken before the fitting. Application of the solver results in the rate constants 0.0031 and 0.00161. Due to the smaller number of data, they are not as well defined as the results of the analysis of the complete data set (p. 165). 

As a last example, we demonstrate the versatility of the Solver by performing a -fitting of the emission data taken from Data Em iss ion. m (p.191). In order to keep the Excel spreadsheet reasonably concise, we selected the data at one wavelength only (500nm). At this wavelength, the correct amplitudes for the two species are 500 and 50 with lifetimes of 10 and 30 time units. Data are available from times 0 to 100 time units. 

In step 3, a multiline-fitting program was run to optimize the pK a values to minimize the sum of residual squares between calculated and observed mobilities from Eq. (17). Figure 2 shows an example of the MS Excel spreadsheet for pK a calculation. The solver function of MS Excel could be used to perform the multiline-fitting analysis. 

The spreadsheet could be programmed to use the TOOLS-SOLVER function to automate the selection of the pressure-gradient parameter, cell B8, to drive the mean nondi-mensional velocity to 1.0. However, our experience in using this spreadsheet is that the iteration proceeds more efficiently if one simply guesses the value of the parameter and watches the value for the calculated mean velocity in cell E9. Making a series of successively more accurate guesses in cell B8 could be used to solve the problem for a new aspect ratio in just a few seconds. Furthermore it is fun to watch the iterations spin by ... 

Figure 13-1 Spreadsheet for mixture of acids and bases uses solver to find the value of pH in cell H13 that satisfies the charge balance in cell El5. The sums [PyH ] + [Py] in cell D17 and [H2T] + [HT ] t- [T2 ] in cell D18 are computed to verify that the formulas for each of the species do not have mistakes. These sums are independent of pH.

Figure 13-6 Spreadsheet using solver for saturated CaF2 at fixed pH values.

Each row of the spreadsheet must be dealt with separately. For example, in row 10. the pH was set to 0 in cell A10 and the initial guessed value of [F ] in cell CIO was 0.000 1 M. Before executing SOLVER, Precision was set to le-16 in SOLVER Options. In SOLVER, Set Target Cell DIP Equal to Value of 0 By Changing Cells CIO. SOLVER changes the value of [F-] in cell CIO to 3.517E-5 to satisfy the mass balance in cell D10. With the correct value of [F ] in cell CIO, the concentrations of [Ca2+1, [CaOH+], [CaF+], [HF], and [OH ] in columns E through I must be correct. 

Figure 13-11 Spreadsheet for saturated BaCjO solver was used to find the pH in cell A11 necessary to make the net charge 0 in cell H23.

Cu by adding the concentrations of all the species. Use SOLVER to vary [Cu2+] in column D so that the total concentration of copper is 0.025 M. You need to use SOLVER for each line in the spreadsheet with a different pH. 

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. 

Spreadsheet using solver to analyze the mixture in Figure 19-2. 

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. 

We solve Equation A for [HT] by using solver in the spreadsheet, with an initial guess of pH = 10 in cell H10. In the tools menu, select solver and choose Options. Set Precision to le-16 and click OK. In the solver window, Set Target Cell E12 Equal To Value of 0 By Changing Cells HIP. Oick Solve and solver finds pH = 10.33 in cell H10. giving a net charge near 0 in cell E12. 

The spreadsheet uses Equation B to find [Ag+] in column C. pH is input in column A. To find the pH of unbuffered solution, we find the pH at which the net charge in column H is zero. We used solver to find that pH = 7.28 in cell A12 makes the net charge in cell HI2 equal to 0. 

---