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