Using the basic Excel Solver, 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:
- 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 Analytic Solver products is a more efficient use of your time. (Analytic Solver has all of our former Premium Solver capabilities, plus much more.) Analytic Solver Upgrade has a limit of 2,000 constraints for linear problems, and 500 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.