Want to get more from Excel, Solver and Analytics?
Download a free trial of full Analytic Solver. Solve much larger models faster, analyze risk with simulation, use Wizards and Guided Mode, get support from our experts.
Get a Free Trial
No credit card required

A constraint such as A1:A5 = integer, where A1:A5 are decision variable cells, requires that the solution values for A1 through A5 must be integers or whole numbers, such as -1, 0 or 2, to within a small tolerance (determined by the Constraint Precision option).  Integer constraints may be used when a fractional solution value, such as 1.5, wouldn’t make sense in your problem – for example, if the decision variable represents how many people to schedule or how many trucks to buy.

A constraint such as A1 = binary is equivalent to specifying A1 = integer, A1 >= 0 and A1 <= 1.  This implies that A1 must be either 0 or 1 at the solution; hence A1 can be used to represent a “yes/no” decision, such as whether or not to build a new manufacturing plant.

A constraint such as A1:A5 = alldifferent, where A1:A5 are decision variable cells, requires that these cells must be integers in the range 1 to N (N = 5 in this example), with each variable different from all the others at the solution.  Hence, A1:A5 will contain a permutation of integers, such as 1,2,3,4,5 or 1,3,5,2,4.  An alldifferent constraint can be used to model problems involving ordering or sequencing of choices, such as the Traveling Salesman Problem.

Integer constraints have many important applications, but the presence of even one such constraint in a Solver model makes the problem an integer programming problem, which may be much more difficult to solve than a similar problem without the integer constraint.

Options for Solving with Integer Constraints

The Solver Options dialog All Methods tab includes a group of options for solving with integer constraints:

  • The Ignore Integer Constraints check box allows you to solve the “relaxation” of an integer programming problem, where the integer, binary, and alldifferent constraints are ignored, but all other constraints are enforced.
  • The Integer Optimality (%) option, sometimes called the “MIP gap”, allows Solver to stop if it finds an integer solution that is within the specified percentage of the best known bound on the optimal solution.  The default value is 1%; if you want a proven optimal solution (which may take considerable extra time), set this option to 0.