Solver tool

We may compare our graphical result with the result obtained from solving for X and y by nonhnear regression fitting of the experimental rate curves to the power law form of eq. (8). Carrying this out using the Excel Solver tool... 

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. 

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. 

The Solver tool of spreadsheets can be used to obtain numerical solutions to a complex equation. For example, to obtain an x solution to the equation j cos(x) exp(-x) = 0 for... 

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. 

Use the Solver tool in a spreadsheet to obtain the roots of the equation X + 3x — 4x — 1 = 0. Make a plot of this function versus x. Compare your results with those in Fig. 5. 

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

The numbers in Table 15.4 are copied from a spreadsheet. As before, the solver tool is used to minimize the sum of the (difference) column by adjusting the values in four cells holding the coefficient values. The results are... 

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. 

Although the Solver approach is more accurate than the graphical visualization, we reconunend using the Solver tool but also, first, doing a graph to have a clear visualization of the problem and see if the function has a minimum or a maximum. 

According to Table 11.2, the time t that minimizes the distance between the planes is t 0.68 [h] and the distance between the planes is D 179 [km]. A more accurate solution can be obtained using the Solver tool of Excel as follows. 

Fig. 11.8 Final screen depicting the solution found by Solver tool...

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. 

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 you see, we have written the equation but we have not included the constraint that limits us to 8,000 h. This will be considered in the Solver tool box that will be shown later. 

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. 

As suggested in the warm-up example, we will first plot a graph to find the shape of the curve and a tentative solution. Then we will use the Solver tool to confirm the graphic solution or get a more accurate solution. 

As explained in the warm-up example, we first choose a cell for v (D9) and assign it a value of 0. Then we write the function in cell G9. Now click on Data and then Solver. In the Solver tool box we set the objective function (G9), choose minimum, Min, then in By Changing Variable Cells, we select D9 and finally click on Solve to get (Fig. 11.26)... 

We assign Q to cell F9 (with a starting value of 1,000) and write the objective function ((/) in cell H9. Then in the Solver tool box we set H9 as the objective function, Max for maximization, and F9 as a changing variable cell. Then the screen of the spreadsheet will look like this (Fig. 11.29) ... 

Clicking on Solve we get (Fig. 11.34) where the optimum value for x is 6.0685 [cm] and V = 4,104.4 [cm ]. Again the approximate solution given in (a) is close to the exact solution obtained with the Solver tool. Solver is not only more accurate but also simpler, although the graphical (or table) solution is much more visually friendly. 

As detailed and explained in the warm-up example (11.5.5) we will use the Solver tool from Microsoft Excel. The following screen shows the objective function (cell L6), the variables R and P (cells 19 and no, respectively), and the constraints for Flour, Capital and Hundredweights (cells D9, DIO and D11 respectively). We tentatively start with initial values of R = 50 and P = 30 (Fig. 11.35). 

Then, as shown in the following screen, in the Solver tool box we include objective function, changing variables and constraints (Fig. 11.36). 

Fig. 11.36 Solver tool box where variables R and P should be integer (19 and 110 = integer)...

---