The Excel Solver Add-In

Solver is an Excel add-in that allows the user to iteratively solve systems of equations. Unfortunately, it is not installed by default on most computers. 

---

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

Thus the transportation problem has 36 variables and 15 constraints. Solving it in Microsoft s Excel Solver add in, we get the optimal solution as shown in Table 5.4. The minimum shipping cost is 34,830. 

Excel has a powerful tool called SOLVER that carries out the minimization for us. You will probably see SOLVER in the TOOLS menu. If not in the TOOLS menu, go to ADD-INS and select Solver Add-In. Click OK. SOLVER will be loaded and will then appear in the TOOLS menu. 

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

The Solver is an Add-In, a separate software package. To save memory, it may not automatically be opened whenever you start Excel. If the Solver Add-in has already been opened, you will see the Solver... command in the Tools menu. If not, open the Solver Add-In by choosing Add-ins... from the Tools menu, check the box for the Solver, then press OK. 

The SolvStat.xls macro is an Auto Open macro when you Open the document it will appear on screen and then Hide itself. It installs a new menu command. Solver Statistics..., directly under the Solver... command in the Tools menu. If the Solver Add-In has not been loaded, the Solver Statistics... command will be at the top of the menu. The command will remain in the menu imtil you exit from Excel. 

The Solver program is generally found under Tools in the Excel spreadsheet. If you do not see it there, it is probably an Add-In, which you have to activate by checking the Solver Add-In box. Once you locate Solver, click on it and you will open the Solver Parameters dialogue box. The filled-in Solver dialogue box for Example 6.1 is shown here ... 

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

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. 

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. 

In Excel 2003, in order to install the Solver add-in, go to Tools —> Add-in. In the window that appears, which is shown in Fig. 8.6, select Solver Add-in and press OK. Excel will then try to install the add-in. This may require the original Excel CD or DVD. 

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. 

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. 

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 actual task of finding the correct free concentrations [M and [L] is undertaken by the Solver. The Solver is a very powerful tool in Excel. It can be employed to maximise and minimise functions of many variables and to find solutions to functions of many variables. The Solver can be found in the Tools menu. If it is not there, it has to be installed as an Add-In, also found... 

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. 

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. 

Step 1 Under the Tools menu, click on Solver. Note If the choice Solver does not appear, choose Add-Ins and load Solver from the Analysis ToolPak or the original Excel program disk (or see your system administrator for help). 

Step 6 The goal is to minimize F17 by choosing values C16 C18. To do that, choose Tools/Solver. You might have to add it to the Excel program if that was not done when the program was installed. A screen appears in which you insert F17 as the quantity to be affected, and choose Min as the option. Then insert CI6 C18 as the cells to be changed, and click Solve. The results are shown in Table E.8. 

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. 

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

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

Observe that one can easily add constraints that limit the amount purchased from subcontractors each month or the maximum number of employees to be hired or laid off. Any other constraints limiting backlogs or inventories can also be accommodated. Ideally, the number of employees hired or laid off should be integer variables. Fractional variables may be justified if some employees work for only part of a month. Such a linear program can be solved using the tool Solver in Excel. 

The Solver Parameters dialog box is shown in Figure 8-5. Click on Solve. The optimal solution should be returned. If Solver does not return the optimal solution, solve the problem again after saving the solution that Solver has returned. (In some cases, mnltiple repetitions of this step may be required because of some flaws in the version of Solver that comes with Excel. Add-ins that do not have any of these issues are available at relatively low cost.) The optimal solution turns out to be the one shown in Table 8-4. 

Solver is a powerful tool that is a standard Excel Add-In. It can produce solutions to many different kinds of problems, among which are the following ... 

---