Eliminating Non-Essential Calculations
During the solution process, the Solver adjusts
only the values of the decision variables (changing cells). Any
calculated cell values which do not depend on the decision variables
will remain constant in the Solver problem. Microsoft Excel
uses "smart" recalculation methods to determine which
cells may have changed their values and therefore need to be
recalculated. However, some time is expended in checking which cells
have changed -- and this time can add up if you have hundreds or
thousands of cells which must be checked.
It is a good idea to design your application so
that the changeable elements of a Solver model are kept by
themselves on one worksheet. You may have many other
calculations in cells on other worksheets, whose values must be
referenced in the Solver model. Although you can refer to these
values through linking formulas such as =Sheet1!A1, such references
are relatively expensive in recalculation time. To achieve the
greatest savings in solution time, you'll need to copy only the
values (not the formulas) from the other worksheets to your Solver
model worksheet, prior to running the Solver. You can automate this
process with a short macro in VBA.
Bear in mind that the Solver searches for an
optimal solution within an N-dimensional "feasible space"
whose boundaries are determined by the constraints. If you can
tighten the constraints -- by increasing lower bounds in >=
constraints and decreasing upper bounds in <= constraints, you
will give the Solver a smaller area to search, which will usually
take less time.
It often pays to add constraints, as long as they
are not redundant -- i.e. if they do indeed reduce the size of the
feasible region. Although the Solver must do more work on every
iteration to process the extra constraints, often the result is that
the solution process takes fewer iterations, for an overall time
savings.
Next: Methods for Nonlinear
Problems
Back to Linear Versus Nonlinear Models
Back to Improving Slow Solution Times
Back to Standard Excel Solver
Support Information
