Standard Excel Solver - Dealing with Problem Size Limits - Continued

Dealing with Problem Size Limits - Continued

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 Frontline's Premium Solver products is a more efficient use of your time. The Premium Solver has a limit of 8192 constraints for linear problems, and 250 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 GRG Solver handles problems with thousands of constraints, in addition to bounds on the variables.

< Back to Standard Excel Solver Support Information