When Excel Solver shows errors like “Set Objective Cell must be a formula”, “Objective cell not valid”, or Solver refuses to optimize, the issue is almost always with the objective function formula. This page explains how to correctly build an objective function, how to fix common mistakes, and includes a complete numerical example.

• Linear Programming & Optimization
• Fix Constraint Formula Errors
• Fix Decision Variable Cell Errors
• Fix Solver “No Solution” Errors

1. What a Correct Objective Function Formula Must Look Like

The objective cell must contain a single Excel formula that combines the decision variable cells. A valid objective formula:

• References the decision variable cells directly.
• Contains no typed numbers except coefficients.
• Evaluates to a single numeric value.
• Does not contain text, errors, or blank references.

Solver cannot optimize if the objective cell contains a number, text, or a formula unrelated to the decision variables.

2. Common Objective Function Formula Errors

2.1 Objective Cell Contains a Typed Number

If the objective cell contains 0 or 100 instead of a formula, Solver cannot optimize because changing variables does not affect the objective.

2.2 Objective Formula Does Not Reference Decision Variables

A formula like =A1 + A2 is invalid if A1 and A2 are not the decision variable cells.

2.3 Wrong Cell References

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

2.4 Objective Formula Contains Errors

Any #VALUE!, #REF!, or #DIV/0! error makes Solver fail instantly.

2.5 Objective Uses Nonlinear Expressions in a Linear Model

A formula like =B4*C4 is nonlinear and cannot be solved by Simplex LP.

3. How to Build a Correct Objective Function

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 — Multiply Each Variable by Its Coefficient

Example objective: \[ Z = 40x_1 + 30x_2 \]

Excel formula: =40*B4 + 30*C4

3.3 Step 3 — Ensure the Formula Evaluates to a Number

The objective cell must always show a numeric value.

3.4 Step 4 — Choose Max or Min Correctly

In Solver:
• Maximize for profit, revenue, output • Minimize for cost, time, distance

3.5 Step 5 — Ensure All Constraints Reference the Same Variables

The objective and constraints must use the same decision variable cells.

4. Numerical Example: Fixing an Objective Function Formula

A company produces two products. Solver fails because the objective formula was set up incorrectly.

4.1 Incorrect Setup

Decision variables (correct):

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

Incorrect objective cell:

• Contains 0 instead of a formula
• Or contains =B4*C4 (nonlinear)
• Or references wrong cells like =40*B5 + 30*C5

Solver cannot optimize because the objective does not depend on the decision variables.

5. Fixing the Objective Function

5.1 Correct Objective Formula

\[ Z = 40x_1 + 30x_2 \]

Excel formula: =40*B4 + 30*C4

5.2 Ensure Constraints Reference B4:C4

Machine time: =2*B4 + C4

Labor: =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 objective formula correctly references the decision variables.

7. Additional Tips for Reliable Objective Formulas

• Never type a number into the objective cell—always use a formula.
• Ensure every coefficient multiplies the correct decision variable.
• Check for hidden text or formatting issues.
• Use Simplex LP for linear models.

For more Solver troubleshooting: