Integer programming models fail in Excel Solver when integrality is not applied correctly, constraints conflict with integer requirements, decision variable cells contain formulas, or the model becomes infeasible after adding integer restrictions. This page explains how to correctly define integer variables, how to fix common errors, and includes multiple numerical examples.

1. What integer programming requires

A valid integer programming model must have:

• Decision variable cells containing numbers only (no formulas).
• A contiguous block of integer variables.
• Constraints referencing those variables correctly.
• Solver set to Simplex LP + Integer Constraints or GRG + Integer depending on linearity.
• No nonlinear expressions involving integer variables unless using GRG Nonlinear.

If any of these conditions fail, Solver may return infeasible, fractional values, or “Solver could not find a feasible solution.”

2. Common integer programming errors

2.1 Integer cells contain formulas

Integer cells must be editable numbers. If they contain =something, Solver cannot change them.

2.2 Integer constraints applied to the wrong range

If Solver is told the wrong cells are integer, the model becomes inconsistent.

2.3 Model feasible in continuous form but infeasible when integer

This is extremely common. Integer restrictions shrink the feasible region and may eliminate all solutions.

2.4 Nonlinear formulas with integer variables

Expressions like =B4*C4 create nonlinearities that Simplex LP cannot solve.

2.5 Wrong inequality direction or missing bounds

Integer models are sensitive to constraint direction; a single wrong sign can eliminate all integer solutions.

3. How to correctly define integer variables

3.1 Step 1 — Create a clean integer variable block

x1   x2   x3

Cells must contain numbers (0 is fine).

3.2 Step 2 — Reference these cells in the objective

Example: \[ \text{Maximize } Z = 6x_1 + 4x_2 + 5x_3 \] Excel: =SUMPRODUCT(B4:D4, B6:D6)

3.3 Step 3 — Add capacity or logical constraints

Example: \[ 3x_1 + 2x_2 + x_3 \le 20 \] Excel: =3*B4 + 2*C4 + 1*D4

3.4 Step 4 — Tell Solver these cells are integer

In Solver → Add constraint → B4:D4 = integer.

4. Numerical example 1 — Integer knapsack model

A company chooses quantities of 3 products. Each product has a weight and profit. The knapsack capacity is 20 units.

4.1 Decision variables

x1 = units of Product 1 (integer)
x2 = units of Product 2 (integer)
x3 = units of Product 3 (integer)

4.2 Data

        P1   P2   P3
Profit   6    4    5
Weight   3    2    1
Capacity = 20

4.3 Objective

Maximize: \[ Z = 6x_1 + 4x_2 + 5x_3 \] Excel: =SUMPRODUCT(B4:D4, B6:D6)

4.4 Constraint

\[ 3x_1 + 2x_2 + x_3 \le 20 \] Excel: =SUMPRODUCT(B4:D4, B7:D7)

4.5 Integer constraint

B4:D4 = integer

4.6 Solution

Solver returns:

• \(x_1 = 4\)
• \(x_2 = 2\)
• \(x_3 = 2\)

Weight = \(3(4) + 2(2) + 1(2) = 20\) Profit = \(6(4) + 4(2) + 5(2) = 42\)

5. Numerical example 2 — Integer production scheduling

A factory must decide how many batches of two products to produce. Batches must be whole numbers.

5.1 Decision variables

x1 = batches of Product 1
x2 = batches of Product 2

5.2 Data

        P1   P2
Profit   50   40
Machine  6    4
Labor    3    5

Machine cap = 60
Labor cap   = 50

5.3 Constraints

Machine: \[ 6x_1 + 4x_2 \le 60 \] Labor: \[ 3x_1 + 5x_2 \le 50 \]

5.4 Objective

Maximize: \[ Z = 50x_1 + 40x_2 \]

5.5 Integer constraint

x1, x2 = integer

5.6 Solution

Solver returns:

• \(x_1 = 6\)
• \(x_2 = 4\)

Machine use = \(6(6) + 4(4) = 52\) Labor use = \(3(6) + 5(4) = 38\) Profit = \(50(6) + 40(4) = 460\)

6. Numerical example 3 — Integer “minimum order size” constraint

A supplier requires that if you order a product, you must order at least 5 units.

6.1 Decision variables

x = quantity ordered (integer)
y = 1 if product is ordered, else 0 (binary)

6.2 Linking constraint

\[ x \ge 5y \]

If \(y = 0\), then \(x \ge 0\). If \(y = 1\), then \(x \ge 5\).

6.3 Common mistake

Using x = 5y forces exactly 5 units, not at least 5.

7. Checklist for fixing integer programming errors

• Ensure integer cells contain numbers, not formulas.
• Use a single contiguous block for all integer variables.
• Verify every constraint references the correct integer cells.
• Check feasibility in continuous form before adding integrality.
• Use SUMPRODUCT for objective and resource constraints.
• Explicitly set integer constraints in Solver.
• Use Simplex LP for linear integer models.