Solver tool in Excel

Please verify for yourself that this equation is correct. If one does not know Cmax or cannot make a reasonable guess, then one must use nonlinear curve fitting techniques, the simplest of which is the Solver tool in Excel. 

Optimize the alkylation process for two objectives (cases A and/or B) using the e-constraint method and Solver tool in Excel. Are the results comparable to those in Figures 1.5 and 1.6 ... 

Optimize the alkylation process for two objectives (cases A and/or B) using the weighting method. One can use the Solver tool in Excel for SOO. Try different weights to find as many Pareto-optimal solutions as possible. Compare and comment on the solutions obtained with those obtained by the -constraint method (Figures 1.5 and 1.6). Which of the two methods - the weighting and the e-constraint method, is better ... 

The optimal location is one that minimizes the total TC in Equation 5.5. The optimal solution for SA is obtained using the Solver tool in Excel (see spreadsheet Figure 5-8), as shown in Figure 5-8. 

The capacity and demand data, along with production, transportation, and inventory costs at different factories for the merged firm TelecomOptic, are given in Table 5-2. The supply chain team decides to solve the plant location model using the Solver tool in Excel. 

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

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 fitting of the experimental data to the model described above was carried out in a commercial worksheet programme (Excel 5 - Microsoft) using a least-squares method. The equations were integrated using the Euler method with a suitable time step, and the sum of the squares of the residuals for all data points in an experiment was minimised using the Solver tool in the software described above. 

If the model is a linear plus pair-wise interactions model, the solution can easily be shown to be one of the vertices of the hypercube in the hypercube approach. If the model is a quadratic one, and the optimum (according to the model) is not inside the hypjercube, the solution is a point on one of the edges of the hypercube and a point on the hyp>ersphere in the hypersphere approach. In both approaches, the solution is found most easily using some iterative constrained optimization tool, e.g. Excel s Solver Tool. In the latter (hypersphere) approach, it is easy to show, using the Lagrange multiplier technique of constrained... 

This should first be done for the experimental data and then, on the same graph, for an assumed value of b, the mean thickness of the amorphous layers. A sensible value to assume is 5-10 nm. This value can then be refined manually, by adjusting the value until a visually, good fit to the data is obtained. Alternatively a least-squares fit can be done, again by manual adjustment or by using a fitting routine such as the Solver tool in Microsoft Excel. The best fit is found for A = 8.1 nm and the graph shows the fit. 

As was mentioned in previous chapters, it is highly reconunended to approach problems, in this case LP problems, with the right attitude and, more importantly, with a methodology. As discussed in Sect. 11.6.4, a right solution procedure includes four steps, as follows Step I. Variable definition and codification. Step II. Formulation of objective function. Step HI. Formulation of all constraints. Step IV. Implementation and solution with the Solver tool (Microsoft Excel). 

As detailed and explained in the previous example, we will use the Solver tool from Excel. The following screen shows the objective function (cell J5), the variables Gpi and Gp2 (cells 19 and no, respectively), and the constraints for catalyst Ci and catalyst C2 (cells Dll and D12, respectively). We tentatively start with initial values of Gpi = 8 [g] and Gpa = 5 [g] (as shown in the screenshot, this is a feasible solution) (Fig. 11.38) ... 

The parameters to be estimated from the curve-fitting are Ct, At (=-7/t) and the constant term Fr in the Nemst equation. An additional possibility is to include Ki among the parameters. The remaining equilibrium constants must be taken from their salinity-temperature dependence equations. Minimization algorithms are now easily available in standard spreadsheet programmes such as the solver tool in Microsoft Excel. 

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

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. 

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. 

An Excel spreadsheet illustrating the use of the Solver tool for nonlinear least-squares analysis of a fluorescent decay curve of a ruby crystal. The sum of the squares of residuals is calculated in cell C14 and is minimized in Solver by iterative variation of the parameters in cells CIO, Cll, and C12. 

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. 

Data analysis modern spreadsheets contain several convenient data-analysis aids, such as Excel s Trendline, a flexible linear least-squares tool, and Excel s Solver, a powerful multi-parameter non-linear least-squares fitting routine. Both of these are described in detail in chapter 3, and are used throughout the remainder of this book. Excel also contains a large number of tools for statistical data analysis. 

Excel s multi-parameter non-linear least-squares routine. Solver, an implementation of the Levenberg-Marquardt algorithm, is a generally useful tool. In section 10.8a we describe an addition that can make it even more useful, and in section 10.8b we briefly indicate how you can call Solver from your macro. 

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

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

Finally, Hartree determined the best k, in equation 5.22, by applying the variation principle. In EXCEL, this stage of the calculation is reproduced very straightforwardly using the SOLVER macro. So activate the SOLVER macro under the TOOLS menu and minimize the total energy in cell F 8 using the scaler, cell D 5, as the variable. 

Solver tool. Use the Solver tool (Excel spreadsheet) to find the maximum or the minimum of the function, in this case the value of t that minimizes D. 

At this point, we would like to reemphasize that our goal is to familiarize you with phase (a), problem formulation. In addition, we will show you how to solve these problems utilizing the Solver tool of Microsoft Excel. 

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. 

Now that we have written the mathematical formulation of the problem in a Microsoft Excel spreadsheet, we can explain how to use the Solver tool box to obtain the optimum solution, in this case a maximum. 

In Excel spreadsheet, click Data in the tool bar. On the right-hand side will appear Solver, as shown in the following image (Fig. 11.13) ... 

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. 

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. 

---