If You've Exceeded Problem Size Limits

The standard Microsoft Excel Solver places upper limits on the number of decision variables (or changing cells), and the number of constraints in a Solver model. The limit on decision variables is straightforward (200), but the limit on constraints depends on the type of model (linear or nonlinear) and the form of the constraints -- so you may be able to modify your model to work within the constraint limit. Alternatively, you can move to one of Frontline's enhanced solvers, which have higher limits on both decision variables and constraints.

Before you reach the standard Solver's size limits, you may find that the solution process takes a long time. Click on this topic to learn about steps you can take to speed up the solution process. Alternatively, you can usually obtain much faster solutions with Frontline's enhanced solvers.

Limits on Decision Variables

The standard Microsoft Excel Solver has a limit of 200 decision variables, for both linear and nonlinear problems. The only way around this limit is to change your model so that some of the cells you have chosen as decision variables are removed from the By Changing Cells reference, and are held constant in the optimization process. To explore the consequences of varying the cells which are no longer determined by the Solver, you'll have to employ what-if analysis or other methods.

Frontline's Premium Solver has a limit of 800 decision variables for linear problems, and 400 variables for nonlinear problems -- and it solves problems of this size in much less time. Our Large-Scale LP Solver will handle linear problems with thousands of decision variables, using memory-efficient sparse matrix techniques.

Limits on Constraints

If you have a linear problem -- and you've checked the Assume Linear Model box in the Solver Options dialog -- there is no limit on the number of constraints. If you have not checked the Assume Linear Model box, the Solver assumes that the problem is nonlinear and imposes a limit.

For nonlinear problems, you can place constraints on up to 100 cells which are not decision variables. In addition, you can place constant upper and/or lower bounds on the decision variables, and you can place integer constraints on some or all of the decision variables.

If you have reached the limit of 100 constraints, consider these steps:

• Is the model necessarily nonlinear? If you can formulate it as a linear model, the constraint limit won't apply. You may be able to use a "piecewise-linear" approximation to a nonlinear function.
• Do you have constraint cells containing simple formulas like =A1 where A1 is a decision variable? Such cells count against the limit of 100; but if you apply the constraint directly to the decision variable cells (and use a constant right hand side), it won't.
• Do you have constraints involving (say) just two decision variables? If so, you might be able to place individual limits on the variables; this may change the range of possible solutions, but still allow you to make some progress.
• Do you have constraints where the right hand side is a cell or cells whose value depends on the decision variables? The Solver treats constraints like A1 <= A2 as if they were written A1-A2 <= 0, and counts them against the constraint limit -- even if A1 is a decision variable. Try to replace these constraints with individual cell limits if possible.
• Do you have constraints where the right hand side (RHS) is a formula, rather than a constant or a reference to cells which are constant? The Solver treats all RHS formulas as variable -- so A1 <= 2+2 would count against the constraint limit, even if A1 is a decision variable.

You may find that upgrading to one of our enhanced solvers is a more efficient use of your time. Frontline's Premium Solver has a limit of 200 constraints for nonlinear problems (apart from bounds on the variables and integer constraints) -- and it solves problems of this size in much less time. Our Large-Scale LP Solver will efficiently solve linear problems with thousands of constraints.

For more information on constraints and the limits on constraints, consult How the Solver Handles Constraints (10K).

Copyright © 1996 Frontline Systems Inc.
Last modified: December 01, 1996