Efficiency of Constraint Forms
The Solver recognizes the case where the constraint left hand side is a decision variable, or a set of decision variables. As long as the corresponding right hand sides are constant (i.e. not dependent on any of the variables), these constraints are specially treated as bounds on the variables. In the solution process, such bounds require considerably less time to satisfy than the more general constraint forms. The most common instance of a bound on a variable is a non-negativity constraint such as A1 >= 0, but any sort of constant bounds are efficiently handled by both the linear and nonlinear Solvers.
There is no difference in terms of efficiency between a constraint entered (for example) as A1 <= 100 or as A1 <= B1 where B1 contains 100; the Solver recognizes that B1 is equivalent to a constant. The form A1 <= B1 is usually better from the standpoint of maintainability of your optimization model. Similarly, there is no difference in efficiency if you use defined names instead of cell references, so this practice is recommended.
On the other hand, a constraint right hand side which is a formula -- even a simple one like 2+2 -- will incrementally increase the solution time for the model.
Because the Solver doesn't have the facilities to recognize the right hand side "on the fly," it treats any formula as a RHS potentially dependent on the variables, and internally creates a constraint "LHS - RHS >= 0" -- even if the formula really was a constant bound on a variable. It is better to place whatever formula you need into a cell, and reference that cell as the constraint right hand side: Because the formula has already been analyzed by Excel when it was entered in the cell, the Solver can determine whether it is dependent on the variables.
Constraints and Fast Problem Setup
You can use any spreadsheet formula, operator or function to compute the values in the left hand sides of constraints -- whether your problem is linear or nonlinear. However, if your problem is linear and involves a significant number of decision variables and/or constraints, it pays to use certain common functions to express the left hand sides -- both for the sake of keeping your model readable and manageable, and to obtain the benefit of fast problem setup offered by Frontline's Premium Solver products.
Any linear function can be written in the form of a single call to the SUMPRODUCT function -- or, if the cells you are referencing are scattered around the worksheet, Frontline's add-in DOTPRODUCT function (if you are registered and logged in, you can download this function for free).
In many simple cases you can use the SUM function, which is equivalent to SUMPRODUCT where one of the two arguments consists of all 1's. If you organize and lay out your model's parameters in columns and rows and you consistently use these simple functions, you will gain many benefits down the road.
Limits on the Number of Constraints
The standard Excel Solver has a limit of 200 decision variables or changing cells. It also imposes a limit on the number of constraints in certain situations. Here are the details: If the problem is linear -- and you have checked the Assume Linear Model box in the Solver Options dialog -- then there is no limit on the number of constraints. If the problem is nonlinear, there is a limit of 100 constraints other than constant bounds on the variables and integer constraints.
So, for example, if A1 is a decision variable, you could specify A1 >= 0, A1 <= 1 and A1 = integer without using up any of the 100 constraints. On the other hand, a constraint such as A1 = 2+2 would count against the limit of 100 constraints, because the Solver treats the right hand side as a formula (even though it is actually a constant value) as noted earlier.
Note that while there is no limit on the number of constraints in a linear problem, in practice it is unusual to have more constraints than decision variables (though you may have both upper and lower bounds on some constraint cells). This is because a problem with more constraints than variables is an overdetermined linear system, and some of the constraints must be redundant (i.e. always satisfied when the other constraints are satisfied).
If your problem exceeds the limits on the number of decision variables and constraints allowed by the standard Excel Solver, it is likely that one of Frontline's Premium Solver products will be able to handle the problem. Even if your problem doesn't exceed these limits, you may find that the standard Solver takes a long time to find the solution; in this case you may find the Premium Solver products worthwhile, since they can be many times faster than the standard Solver.