Excel solver constraints

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

As the Excel Solver is only for single objective optimization, use the e-constrained method and the Excel Solver.xls on the CD to optimize the 4-plant IE for the two objectives as in Cases A and B. For the Solver to work reliably, number of decision variables should be limited. Thus, it is recommended to set Z21 = Z32 = 0 and Z22 = 1 = 1 for Z = a, b, c and d. This would leave the capacities of the 4 plants (Xj) as the decision variables. Treat lEvP as the constraint and vary it in the range 1.213-1.419 for Case A and 1.220-1.321 for Case B, and observe the trends of the decision variables and the objective. Do they follow similar trends as the IE for 6 plants ... 

For our example the LP has twelve variables and six constraints. The optimal solution can be found using simplex algorithm or common software such as excel solver employing similar algorithms. 

Table 4.12 contains the required input data for excel solver. Lighter shaded cells include variable values. Darker shaded cells include excel formulations. The dark shaded cell corresponding to objective function (Obj) has the excel expression = sumproduct column ri + Si), column wi)). Constraint 1 LHS (left hand... 

IP problems are hard problems (technically called NP-hard, 0-1 IP problems are NP-complete). The solution time for the problem grows enormously with the size of the problem (number of variables, number of constraints). One can imagine the intractability in enumerating all solutions and picking the best one as the optimal solution. Excel solver can be used to solve small instances of the problem. 

As we did for the OAK-ATL lane, we will assume that (x,- = The constraints for this problem as the same as those for Example 4.3 (i.e., expressions (4.8 through 4.10)). The Excel Solver set-up for the problem is similar to that shown in Figure 4.10. 

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. 

This mixed integer program with 18 binary variables, 16 continuous variables, and 38 constraints was solved using Excel Solver. The optimal order allocation is given in Table 6.11. Both suppliers are used for purchases. The policy results in a total cost of 93,980. 

As in previous situations, we use the Excel Solver to solve the system of equations. The mass balance is considered as the target cell, while the momentum and Colebrook equations will be used as constraints. 

In the proposed framework, the objective functions are formulated in Excel for the modelled process in HYSYS. The multi-objective optimisation technique, e-constraint, is formulated with the Premium Solver Platform (by Frontline Systems), which is an upgrade of the standard Excel solver, that uses the standard non-linear GRG (Generalized Reduced Gradient) method. However, any other optimisation method, such as that mentioned earlier, can be easily formulated in Excel and evaluated accordingly. 

To set up the linear programming problem, formulate an objective function and constraints for the refinery operation. From Fig. 19.7, six variables are involved, namely, the flow rates of the two raw materials and the four products. Solve the LP using the Excel Solver. 

Although there are two independent variables in this problem (six variables and four equality constraints), there is no need to carry out variable substitution or further simplification, because the Excel Solver can easily handle the solution of this fairly small NLP problem. 

Excel Tip. Don t introduce constraints (e.g., to force a constant to be greater than or equal to zero) if you re using the Solver to obtain the least-squares best fit. The solution will not be the "global minimum" of the error-square sum, and the regression coejficients may be seriously in error. 

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

Kinetic and electrochemical data, respectively, were fitted to eqs 28 and 29. Non-linear least-square fits of the observed rate constant and the formal redox potential versus [Ml were carried out using the Solver Function in Microsoft Excel-98. Sums of deviation-squared values were minimized by varying k, kivir, Ko, Kred, Eo, a and b in eqs 28-30. The ratios Yox/y ox and Yred/y rcd were assigned a value of 1. Additional limitations and constraints imposed on the adjustable parameters to improve fitting are discussed above. 

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

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. 

---