Assignment models fail in Excel Solver when the one‑to‑one constraints are built incorrectly, the SUM formulas reference the wrong cells, or the binary decision variable grid is not properly defined. This page shows how to build correct assignment constraints, how to fix common structural errors, and includes a complete numerical example.

• Linear Programming & Optimization
• Parent Business Analytics
• Fix Solver Infeasible Errors
• Fix Decision Variable Cell Errors

1. What Assignment Model Constraints Must Look Like

An assignment model requires two sets of constraints:

• Each worker is assigned to exactly one job (row constraints).
• Each job is assigned to exactly one worker (column constraints).

These constraints must be written as SUM formulas over the binary decision variable matrix.

If any SUM range is wrong—or if the constraints use ≥ instead of =—Solver will return infeasible or incorrect results.

2. Common Assignment Constraint Errors

2.1 SUM Ranges Reference Wrong Cells

A single incorrect SUM range (e.g., SUM(B4:D4) instead of SUM(B4:E4)) breaks the entire model.

2.2 Using ≤ Instead of =

Assignment models require exactly one, not “at most one.”

2.3 Missing Binary Constraints

If decision variables are not restricted to 0/1, Solver may assign fractional values.

2.4 Decision Variable Cells Contain Formulas

Assignment variables must be numbers only (0 or 1). Formulas cause Solver to fail.

2.5 Cost Matrix Misaligned with Decision Variables

If the cost table does not match the assignment table cell‑for‑cell, the objective becomes incorrect.

3. How to Build Correct Assignment Constraints

3.1 Step 1 — Create the Assignment Matrix

        Job1 Job2 Job3 Job4
    W1   x11  x12  x13  x14
    W2   x21  x22  x23  x24
    W3   x31  x32  x33  x34
    W4   x41  x42  x43  x44
  

All xᵢⱼ cells must be blank numbers (0 is fine).

3.2 Step 2 — Add Row Constraints (One Job per Worker)

Example for Worker 1: =SUM(B4:E4) must equal 1.

3.3 Step 3 — Add Column Constraints (One Worker per Job)

Example for Job 1: =SUM(B4:B7) must equal 1.

3.4 Step 4 — Add Binary Constraints

In Solver:
B4:E7 → binary

3.5 Step 5 — Build the Objective Function

Objective cell formula: =SUMPRODUCT(B4:E7, B10:E13)

4. Numerical Example: Fixing Broken Assignment Constraints

A company assigns 4 workers to 4 jobs. Solver reports infeasible because the constraints were built incorrectly.

4.1 Incorrect Setup

• Row SUM formulas reference only 3 jobs instead of 4.
• Column SUM formulas reference wrong rows.
• Constraints use ≤ instead of =.
• Decision variable cells contain formulas.
• Binary constraints missing.

5. Fixing the Assignment Constraints

5.1 Correct Row Constraints

For each worker:
=SUM(B4:E4) = 1 =SUM(B5:E5) = 1 =SUM(B6:E6) = 1 =SUM(B7:E7) = 1

5.2 Correct Column Constraints

For each job:
=SUM(B4:B7) = 1 =SUM(C4:C7) = 1 =SUM(D4:D7) = 1 =SUM(E4:E7) = 1

5.3 Add Binary Constraints

In Solver:
B4:E7 → binary

5.4 Ensure Objective Uses SUMPRODUCT

=SUMPRODUCT(B4:E7, B10:E13)

6. Solving the Corrected Model

After fixing the constraints, Solver finds a valid one‑to‑one assignment. Example:

• x₁₂ = 1
• x₂₄ = 1
• x₃₁ = 1
• x₄₃ = 1
• All other xᵢⱼ = 0

Objective value: \[ Z = \sum c_{ij} x_{ij} \]

The model works because the assignment constraints are now correctly defined.

7. Additional Tips for Reliable Assignment Models

• Always use “= 1” for assignment constraints.
• Ensure every SUM range covers the correct row or column.
• Use SUMPRODUCT for the objective function.
• Keep decision variables in a clean, contiguous block.
• Use Simplex LP + Binary constraints.

For more Solver troubleshooting: