Standard Excel Solver - How the Solver Handles ConstraintsHow the Solver Handles ConstraintsThis page provides more insight into the details of how the Solver handles constraints, which can help you select the most efficient forms of constraints, and determine when the Solver's limits on the number of constraints may be exceeded.
Form and Meaning of ConstraintsAs noted in the Solver Tutorial, constraints are relations such as A1 >= 0. A constraint is satisfied if the condition it specifies is true within a small tolerance, specified by the Precision setting in the Solver Options dialog. This is a little different from a logical formula such as ... =A1>=0 ... evaluating to TRUE or FALSE which you might enter in a cell. In this example, if A1 were -0.0000001, the logical formula would evaluate to FALSE, but with the default Precision setting, the constraint would be satisfied. Because of the numerical methods used to find solutions to Solver models and the finite precision of computer arithmetic, it would be unrealistic to require that constraints like ... A1 >= 0 ... be satisfied exactly -- such solutions would rarely be found. Another type of constraint is of the form A1 = integer, where A1 is one of the decision variables. This specifies that the solution value for A1 must be an integer or whole number such as -1, 0 or 1 to within a small tolerance (also the Precision setting). The presence of even one such integer constraint in a Solver model makes the problem an integer programming problem, which may be much more difficult to solve than the equivalent problem without the integer constraint. (See Optimization Problem Types - Mixed-Integer and Constraint Programming). Constraint Left and Right Hand SidesIn the Excel Solver, constraints are specified by giving a cell reference such as A1 or A1:A10 (the "left hand side"), a relation (<=, = or >=), and an expression for the "right hand side." Although Excel allows you to enter any numeric expression on the right hand side, we strongly encourage you to use only constants, or references to cells which contain constant values on the right hand side. (A constant value to the Solver is any value which does not depend on any of the decision variables.) Using constant right hand sides in constraints will simplify your model, and is essential to obtain the benefits of fast problem setup in Frontline's Premium Solver products. The constraint left hand side, entered in the Cell Reference edit box of the Add Constraint or Change Constraint dialog, may be any individual selection, such as a column, row, or rectangular area of cells. Multiple selections are not permitted here. The constraint right hand side may be any of the following:
Defined names may be used in lieu of cell references or cell ranges, and this practice is recommended to make your model more readable and maintainable. If you use option 3, a selection of more than one cell, the number of cells selected must match the number of cells you selected for the constraint left hand side. The two selections need not have the same "shape:" For example, the left hand side could be a column and the right hand side a row. You may also use rectangular areas of cells. In any case, when you use this form you are specifying several constraints at once, and the constraint left hand sides correspond element-by-element to the right hand sides. You can see examples of this form in nearly all of our example Solver models. It is by far the most useful form. If the constraint right hand side is a cell reference, cell selection or formula, the Solver needs to know whether the contents of those cells, or the value of the formula is constant in the problem, or variable (i.e. dependent on the values of the decision variables). If the right hand side depends on any of the decision variables, the Solver transforms a constraint such as ... LHS >= RHS into LHS - RHS >= 0 ... internally. Both the linear and nonlinear Solvers work internally with constant bounds on the constraint functions. |
Solver.com
From Frontline Systems, developers of the Excel Solver.
|
||
Solver tutorials
Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your model.
Copyright © 2009 Frontline Systems, Inc.