Page 680 - Excel 2013 All-in-One For Dummies

662 Using the Solver
Before having Solver adjust your model, you may add constraints for the target cell or any of the changing cells that determine its limits when adjusting the values.
5. (Optional) Click the Add button to the right of the Subject to the Constraints list box in the Solver Parameters dialog box.
This action opens the Add Constraint dialog box. When defining a con- straint, choose the cell whose value you want to constrain or select the cell in the worksheet or enter its cell reference in the Cell Reference text box. Then select the relationship (=, <=, >=, or int for integer or bin for binary) from the drop-down list box to the right and (unless you chose int or bin) enter the appropriate value or cell reference in the Constraint text box.
To continue adding constraints for other cells used by the Solver, click the Add button to add the constraint and clear the text boxes in the Add Constraint dialog box. Then, repeat Step 5 to add a new constraint. After you finish defining constraints for the target cell and changing values in the model, click OK to close the Add Constraint dialog box and return to
the Solver Parameters dialog box (which now lists your constraints in the Subject to the Constraints list box).
6. (Optional) Deselect the Make Unconstrained Variables Non-Negative check box if you want to allow negative values when the variable cells are not subject to constraints.
By default, the Solver Add-in employs the GRG (Generalized Reduced Gradient) Nonlinear method in solving the model whose parameters you’re setting known as a very efficient way to solve smooth nonlinear problems. To use the LP Simplex method (for Linear Programming fol- lowing the Simplex algorithm) or Evolutionary engine for solving non- smooth problems, you need to follow Step 7.
7. (Optional) Select LP Simplex or Evolutionary from the Select a Solving Method drop-down list to use either one of these methods solving non- smooth problems.
8. Click the Solve button to have the Solver solve the problem as you’ve defined it in the Solver Parameters dialog box.
Solving the problem
When you click the Solve button, the Solver Parameters dialog box disap- pears, and the status bar indicates that the Solver is setting up the problem and then keeps you informed of the progress in solving the problem by showing the number of the intermediate (or trial) solutions as they are tried. To interrupt the solution process at any time before Excel calculates the
last iteration, press the Esc key. Excel then displays the Show Trial Solution dialog box, informing you that the solution process has been paused. To continue the solution process, click the Continue button. To abort the solu- tion process, click the Stop button.