Constraint formula errors are one of the most common reasons Excel Solver fails. Messages like “Constraint must be a formula”, “Cell values do not satisfy constraints”, or Solver returning unchanged values usually mean the constraint cells are not set up correctly. This page explains how to build valid constraint formulas, how to fix common mistakes, and includes a full numerical example.

• Linear Programming & Optimization
• Fix Solver Infeasible Errors
• Fix Solver Unbounded Errors
• Fix Solver No Solution Errors

1. What a Valid Constraint Formula Must Look Like

Every constraint in Solver must reference the decision variable cells through a formula. A valid constraint cell:

• Contains a formula (not a typed number).
• References the decision variable cells directly or indirectly.
• Evaluates to a single numeric value.
• Does not contain text, errors, or blank cells.

Solver compares the value of the constraint formula to the RHS you specify in the Solver dialog.

2. Common Constraint Formula Errors

2.1 Constraint Cell Contains a Typed Number

If a constraint cell contains 100 instead of =2*B4 + C4, Solver cannot evaluate how changing variables affects the constraint.

2.2 Constraint Formula Does Not Reference Decision Variables

A formula like =A1 + A2 is invalid if A1 and A2 are not decision variables.

2.3 Constraint Formula Contains Text or Errors

Cells with #VALUE!, #REF!, or hidden text will break Solver.

2.4 Wrong Inequality Direction in Solver

The formula may be correct, but Solver may be set to ≥ instead of ≤.

2.5 Formula References Wrong Cells

A common mistake is referencing B5:C5 instead of B4:C4, or mixing absolute and relative references.

3. How to Build Correct Constraint Formulas

3.1 Step 1 — Identify the Decision Variable Cells

Place all decision variables in a contiguous range (e.g., B4:C4).

3.2 Step 2 — Write Each Constraint as a Formula

Example machine‑time constraint:

\[ 2x_1 + x_2 \le 100 \]

Excel formula: =2*B4 + C4

3.3 Step 3 — Put the RHS in Solver, Not in the Cell

The constraint cell should contain only the LHS formula. The RHS (e.g., 100) goes into Solver.

3.4 Step 4 — Ensure All Constraint Cells Are Numeric

The formula must evaluate to a number at all times.

3.5 Step 5 — Add Nonnegativity Constraints

Add B4 ≥ 0 and C4 ≥ 0 unless negative values are allowed.

4. Numerical Example: Fixing Constraint Formula Errors

A company produces two products. Solver fails because the constraint formulas were set up incorrectly.

4.1 Incorrect Setup

Decision variables (correct):

• B4 = 0 (x₁)
• C4 = 0 (x₂)

Objective (correct):

=40*B4 + 30*C4

Incorrect constraint formulas:

• Machine time cell contains 100 instead of =2*B4 + C4
• Labor cell contains =B5 + C5 (wrong row)

Solver cannot evaluate how changing B4 and C4 affects these constraints → error.

5. Fixing the Constraint Formulas

5.1 Correct Machine Time Constraint

\[ 2x_1 + x_2 \le 100 \]

Excel formula: =2*B4 + C4

5.2 Correct Labor Constraint

\[ x_1 + x_2 \le 80 \]

Excel formula: =B4 + C4

5.3 Add Nonnegativity

In Solver:
B4 ≥ 0 C4 ≥ 0

6. Solving the Corrected Model

Solve the two binding constraints:

2x₁ + x₂ = 100
x₁ + x₂ = 80
  

Subtract:

x₁ = 20
  

Substitute:

20 + x₂ = 80  ⇒  x₂ = 60
  

Optimal solution:

• x₁ = 20
• x₂ = 60
• Profit: \[ Z = 40(20) + 30(60) = 2600 \]

Solver now works because the constraint formulas correctly reference the decision variables.

7. Additional Tips for Reliable Constraint Formulas

• Never type numbers into constraint cells—always use formulas.
• Ensure every constraint references the decision variable cells.
• Check for hidden text, spaces, or formatting issues.
• Use Simplex LP for linear models.

For more Solver troubleshooting: