The Solver recognizes the case where the constraint left hand
side is a decision variable, or a set of decision variables. As
long as the corresponding right hand sides are constant (i.e. not
dependent on any of the variables), these constraints are
specially treated as bounds on the variables. In the solution
process, such bounds require considerably less time to satisfy
than the more general constraint forms. The most common instance
of a bound on a variable is a non-negativity constraint such as
A1 >= 0, but any sort of constant bounds are efficiently
handled by both the linear and nonlinear Solvers.
There is no difference in terms of efficiency between a
constraint entered (for example) as A1 <= 100 or as A1 <=
B1 where B1 contains 100; the Solver recognizes that B1 is
equivalent to a constant. The form A1 <= B1 is usually better
from the standpoint of maintainability of your optimization
model. Similarly, there is no difference in efficiency if you use
defined names instead of cell references, so this practice is
recommended.
On the other hand, a constraint right hand side which is a
formula -- even a simple one like 2+2 -- will incrementally
increase the solution time for the model. Because the Solver
doesn't have the facilities to recognize the right hand side
"on the fly," it treats any formula as a RHS
potentially dependent on the variables, and internally creates a
constraint "LHS - RHS >= 0" -- even if the formula
really was a constant bound on a variable. It is better to place
whatever formula you need into a cell, and reference that cell as
the constraint right hand side: Because the formula has already
been analyzed by Excel when it was entered in
the cell, the Solver can determine whether it is dependent on the
variables.
You can use any spreadsheet formula, operator or function to
compute the values in the left hand sides of constraints --
whether your problem is linear or nonlinear. However, if your
problem is linear and involves a significant number of decision
variables and/or constraints, it pays to use certain common
functions to express the left hand sides -- both for the sake of
keeping your model readable and manageable,
and to obtain the benefit of fast
problem setup offered by Frontline's Premium Solver
products.
Any linear function can be written in
the form of a single call to the SUMPRODUCT function -- or, if
the cells you are referencing are scattered around the worksheet,
Frontline's add-in DOTPRODUCT function (if you are registered
and logged in, you can download this function for
free).
In many simple cases you can use the SUM function, which is
equivalent to SUMPRODUCT where one of the two arguments consists
of all 1's. If you organize and lay out your model's parameters
in columns and rows and you consistently use these simple
functions, you will gain many benefits down the road.
The standard Excel Solver has a limit of 200 decision
variables or changing cells. It also imposes a limit on the
number of constraints in certain situations. Here are the
details: If the problem is linear -- and you have checked the
Assume Linear Model box in the Solver Options dialog -- then
there is no limit on the number of constraints. If the
problem is nonlinear, there is a limit of 100 constraints other
than constant bounds on the variables and integer
constraints.
So, for example, if A1 is a decision variable, you
could specify A1 >= 0, A1 <= 1 and A1 = integer without
using up any of the 100 constraints. On the other hand, a
constraint such as A1 = 2+2 would count against the limit of 100
constraints, because the Solver treats the right hand side as a
formula (even though it is actually a constant value) as noted
earlier.
Note that while there is no limit on the number of constraints
in a linear problem, in practice it is unusual to have more
constraints than decision variables (though you may have both
upper and lower bounds on some constraint cells). This is because
a problem with more constraints than variables is an overdetermined
linear system, and some of the constraints must be redundant
(i.e. always satisfied when the other constraints are satisfied).
If your problem exceeds the limits on the number of decision
variables and constraints allowed by the standard Excel Solver, it is likely that one of Frontline's Premium
Solver products will be able to handle the
problem. Even if your problem doesn't exceed these limits, you
may find that the standard Solver takes a long time to find the
solution; in this case you may find the Premium Solver products worthwhile, since they can be many times faster than the standard
Solver.
Back to Standard Excel Solver
Support Information
