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.

Adding or Tightening Constraints

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