When Excel Solver shows errors like “Set Cell Must Be a Formula”, “Decision Variable Cells Must Be Adjustable”, or Solver simply refuses to change your variable cells, the issue is almost always with how the decision variable cells are defined. This page explains how to correctly set up decision variable cells, how to fix common mistakes, and includes a full numerical example.

• Linear Programming & Optimization
• Parent Business Analytics
• Inventory & Supply Chain Analytics
• Decision Analysis & Simulation

1. What Decision Variable Cells Must Look Like

Decision variable cells are the cells Solver is allowed to change. They must:

• Contain numbers only (no formulas).
• Be referenced by the objective cell and constraint formulas.
• Be in a contiguous range (e.g., B4:C4 or B4:D4).
• Not contain text, spaces, or formatting that prevents editing.

If any decision variable cell contains a formula, Solver cannot adjust it and will fail.

2. Common Errors Related to Decision Variable Cells

2.1 Decision Variable Cells Contain Formulas

Solver can only change raw numbers. If a decision variable cell contains =something, Solver cannot modify it.

2.2 Decision Variable Cells Are Not Referenced in the Model

If the objective or constraints do not reference the decision variable cells, Solver will not know how changing them affects the model.

2.3 Non‑Contiguous Decision Variable Ranges

Solver requires a single block of cells. You cannot select B4, D4, F4 separately.

2.4 Hidden Text or Formatting in Cells

Cells containing spaces, apostrophes, or text formatted as numbers will cause Solver to fail.

2.5 Locked or Protected Cells

If the worksheet or cells are protected, Solver cannot adjust them.

3. Step‑by‑Step Fixes for Decision Variable Cell Errors

3.1 Step 1 — Replace Formulas with Numbers

• Ensure each decision variable cell contains a number (0 is fine).
• Move formulas to separate calculation cells.

3.2 Step 2 — Make the Range Contiguous

• Place all decision variables in one row or column.
• Example: B4:C4 or B4:D4.

3.3 Step 3 — Check All References

• Objective cell must reference the decision variable cells.
• Constraint formulas must reference them as well.

3.4 Step 4 — Add Nonnegativity or Bounds

• Set ≥ 0 unless negative values are allowed.
• Add upper bounds if needed.

3.5 Step 5 — Ensure Cells Are Editable

• Remove sheet protection.
• Clear formatting or hidden characters.

4. Numerical Example: Fixing Decision Variable Cell Setup

A company produces two products. Solver reports errors because the decision variable cells were set up incorrectly.

4.1 Incorrect Setup

Decision variable cells (incorrect)

• B4 contains =B1
• C4 contains =C1

Objective

\[ Z = 40x_1 + 30x_2 \]

Constraints

• Machine time: \[ 2x_1 + x_2 \le 100 \]
• Labor: \[ x_1 + x_2 \le 80 \]

Because B4 and C4 contain formulas, Solver cannot adjust them → error.

5. Fixing the Model

5.1 Replace Formulas with Numbers

Set: \[ x_1 = 0,\quad x_2 = 0 \] in B4 and C4.

5.2 Ensure Objective References B4:C4

Objective cell formula: =40*B4 + 30*C4

5.3 Ensure Constraints Reference B4:C4

Machine time: =2*B4 + C4

Labor: =B4 + C4

5.4 Add Nonnegativity

In Solver → Add: \[ B4 \ge 0,\quad C4 \ge 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_1 = 20\)
• \(x_2 = 60\)
• Profit: \[ Z = 40(20) + 30(60) = 2600 \]

Solver now works because the decision variable cells are correctly defined.

7. Additional Tips

• Never put formulas in decision variable cells.
• Keep all decision variables in one contiguous block.
• Ensure every formula references the decision variable cells.
• Use Simplex LP for linear models.

For more Solver troubleshooting: