Using the Solver Add-In

In order to start Solver, in Excel 2007 or newer, locate the Data ribbon and go to the extreme right-hand side in the area marked Analysis. Solver should be there as shown in Fig. 8.7. In Excel 2003 or older, go to Tools Solver. 

Objective Function Value this is the value of the objective function that is to be 

Type of Optimisation what type of optimisation is desired maximisation (Max), minimisation (Min), or force the solver to obtain a particular value (Value of). For regression, the minimisation option should be used. 

Variables this is the range of the cells (variables) that the computer can vary to determine the solution. For regression, this would represent the cells where the parameter values have been entered. 

Constraints this box lists the constraints for the problem. In order to add a constraint, click on the Add button. The window shown in Fig. 8.9 should appear. Once the desired form of the constraint has been selected, click Add to add the constraint to the list of constraints. Selecting a constraint from the box and clicking Change will cause the same window to appear and the properties of the constraint can be changed. Finally, selecting a constraint and clicking Delete will remove the constraint. 

---

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

There is an additional major advantage in using the Solver Add-in and the SolvStat.xls macro, even for functions that are linear or can be rearranged to a linear form. For example, if the function is... 

For representative purposes, both the models described above were formulated in Microsoft Excel and solved using the Solver add-in. 

In our experience, the last end points of a typical 5-point TBP curve (the end point or EBP, 90% vaporization point, and 70% vaporization point), the molecular weight (measured or estimated from API correlation) and specific gravity are good candidate bulk projjerties to rninirnize against This is a basic optimization problem. We have used the SOLVER add-in in Microsoft Excel with considerable success. We note that once an optimized solution has been reached for a base feed, it is often very simple (even manually) to adjust the parameters of the statistical distribution to fit a new feed type. We report the optimal values for the fitting parameters in Table 5.10. 

We are interested in estimating the parameters of the location-specific model, that is the probabihties Pxi sj and the shock rates fMj, for i = 1,2 and j = 1,2. The estimators for the model parameters are the values that minimise the distance given in Relationship (13). To determine these values and for the purposes of illustration, the Solver add-in in Excel has been used. A range of sensible starting values has been considered, to ensure that the solution is not being compromised by local optima. The estimators obtained by using this technique are, for the probabilities ... 

In a very fast adsorption situation, it is too difficult to measure the adsorption rate in the timescale of kinetic experiments. In such situations, it is better to provide a qualitative discussion of the kinetic results. The q and the h along with the fcg can be determined from the non-linear fitting of the data using a solver add-in function of MS Excel for Windows. 

The Data Analysis add-in in Excel is another very useful Excel add-in that can improve the ability to perform certain statistical tasks. It is installed using the same procedure as installing the Solver add-in (see Sect. 8.4.1 Installing the Solver Add-In). In order to start Solver, in Excel 2007 or newer, locate the Data ribbon and go to the extreme right-hand side in the area marked Ana ly s i s. Solver should be there as shown in Fig. 8.7. In Excel 2003 or older, go to Tools —> Data Analysis. 

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

Fitting tasks of a modest complexity, like the one just discussed, can straightforwardly be performed in Excel using the Solver tool provided as an Add-In method. The Solver tool does not seem to be very well known, even in the scientific community, and therefore we will briefly discuss its application based on the example above. As with MATLAB, we assume familiarity with the basics of Excel. 

In what follows we will assume that Windows and Excel have been installed in their complete, standard forms. For some applications we will also use the Solver and the Analysis Toolpak. These come with Excel, but (depending on the initial installation) may have to be loaded as an add-in. 

Most chapters start with a brief summary of the theory in order to put the spreadsheet exercises in perspective, and to define the nomenclature used. The standard versions of Excel 95 through Excel 2000 for Windows 95 or Windows 98 are used. Many exercises use the Solver and the Analysis ToolPak, both of which are available in the standard Excel packages but may have to be loaded separately, as add-ins, in case this was not done initially. When use of chapter 10 is contemplated, the VBA help file should also be loaded. 

After assuming that the benchmark is efficient, it is possible to calculate expected returns for each stock in a portfolio. It has to be emphasized that due to the correlations between assets, changing one of the expected returns results in adjusted optimum weights of the whole portfolio. Having two or more opinions about the asset returns complicates the situation, as the problem cannot be easily implemented onto a spreadsheet. We use Excel s add-in Solver which we integrate in a macro in order to simulate the efficient portfolio weights. 

Solution to the LP model The LP model resulted in 54 variables and 34 constraints. The LP model was solved using Microsoft Excel s Solver add-in in seconds. The optimal solution is given in Tables 2.11 and 2.12. 

This is a particularly interesting feature that is widely used along the examples. It is the tool we can use within Excel to solve numerically a set of equations, problon optimization including fitting a set of data to a given linear and nonlinear equation and more. Solver is an add-in that needs to be activated to be used. For enabling it, we need to click on Office Button (top-left comer), then Excel Options, and in the Tab Adds-Ins, click on bottom go (Manage Excel Adds-Ins) there, look for... 

To obtain a standard deviation to express an experimental error with a confidence area, procedures to obtain standard deviations of parameters of general functions are indispensable. These are, however, quite complex. With the aid of spreadsheet software, the standard deviations can be obtained relatively easily. In this case, a convenient way to obtain the standard deviation is to apply the Solvstat macro to the spreadsheet. Doing so is easy and provides reliable results. First, a spreadsheet to obtain regression coefficients by using Solver must be prepared. Then the Solvstat add-in can be used to elucidate standard deviations of regression coefficients. ... 

The above set of odes is now solved, choosing some algorithm. Nothing has been specified about the homogeneous chemical reaction function F(C), but it will add terms to the matrix W when specified. After the time derivative is discretised in some way, the equation can be rearranged into the same form as described in Chap. 8 and solved using the same methods or, as mentioned above, solved using a professional ode or DAE solver. 

Since Eq. (16) is nonlinear, one must use a nonlinear least-squares fitting program or, as described here, make use of the Solver option available as an add-in tool in Excel. An example of the use of Solver is given in Chapter HI. In the present apphcation, initial estimated values for the four fitting parameters (Q, Cj, P, and j8) are entered into four worksheet cells. For each of the Ndata points, these cells are used to calculate r and then to obtain a theoretical < >obs value from Eq. (16). The difference between the experimental and theoretical < >obs value (residual) is squared and the sum of these squares (essentially proportional to is placed in a test location. Solver is then run iteratively to adjust the fitting parameters so as to minimize this sum of residuals squared. 

The standard installation of Microsoft Office does nol include two extra items the Analysis Tool Pack , and the Frontline Systems SOLVER macro. Since the GT Calculator files require complex arithmetic, the Analysis Tool Pack musl be present. Since the EXCEL Hiickel and Extended Hiickel programmes depend on optimization as required by the application of the variation principle lo flic LCAO-MO Hamiltonian, the SOLVER macro, also, is needed. Both can be added to an existing installation of the OFFICE software using the Add-ins option in the TOOLS menu. 

The operation of the Solver is much harder to illustrate, because it is a multiparameter adjustment. Moreover, it is a much more sophisticated routine, capable of using several different optimizing algorithms. It can even include constraints on the variables. In the previous chapters we already used Solver extensively, and we will here only add a few comments about it. 

Excel offers a number of statistical functions, listed under the Tools menu. Go to Add-Ins, and check Analysis ToolPak. Click OK and return to the spreadsheet. Now when you go to the Tools menu, you will see Data Analysis. Go to that, and you will see 19 statistical programs listed. As you experiment with these, you will find some very useful. One Add-In that is very useful is Solver, for solving complicated formulas. Its use is described in Chapter 6. See also the text website www.wiley. com/college/christian for a list of some commercial software packages for performing basic as well as more advanced statistical calculations. 

For finer control and to introduce two extra useful macros in EXCEL, use the GOALSEEK and SOLVER wizards in the TOOLS drop-down menu to activate internal minimization routines on the least-squares integral in cell F 9 [SOLVER is not a standard item loaded in a typical installation of the EXCEL program, so you may have to enable it using the Add-In option in the TOOLS menu]. 

Optimization model was built in Excel spreadsheet as proposed. Inputs for the optimization were 30-days average mean return for each share, variance-covariance matrix, and initial investment (at the beginning of each month). Excel add-in Solver was implemented into the macro and used to minimize portfolio s variance at the beginning of the each month. For each optimization. Solver was calibrated as the minimum of... 

It is assumed that the software for Solver has been installed in Microsoft Word initially or as an add-in. Accordingly, using the top toolbar under Tools, select Solver to obtain the pull-down menu. 

The implicit multistep methods add stability but require more computation to evaluate the implicit part. In addition, the error coefficient of the Adams-Moulton method of order k is smaller than that of the Adams Bashforth method of the same order. As a consequence, the implicit methods should give improved accuracy. In fact, the error coefficient for the imphcit fourth-order Adams Moulton method is 19/720, and for the explicit fourth-order Adams Bashforth method it is 251/720. The difference is thus about an order of magnitude. Pairs of exphcit and implicit multistep methods of the same order are therefore often used as predictor-corrector pairs. In this case, the explicit method is used to calculate the solution,, at v +i. Furthermore, the imphcit method (corrector) uses y + to calculate /(x +i,y +i), which replaces /(x +i,y +i). This allows the solution, y +i, to be improved using the implicit method. The combination of the Adams Bashforth and the Adams Moulton methods as predictor orrector pairs is implemented in some ODE solvers. The Matlab odel 13 solver is an example of a variable-order Adams Bashforth Moulton multistep solver. 

---