Blending and production models fail in Excel Solver when capacity constraints are mis‑written, ingredient proportions are incorrect, SUMPRODUCT formulas reference the wrong cells, or decision variables are not properly defined. This page shows how to build correct blending and production constraints, how to fix common structural errors, and includes a complete numerical example.

• Linear Programming & Optimization
• Inventory & Supply Chain Analytics
• Fix Constraint Formula Errors
• Fix Solver Infeasible Errors

1. What Blending and Production Models Require

Blending and production models combine multiple inputs (ingredients, materials, labor, machine time) to produce one or more outputs. A correct model requires:

• A decision variable for each ingredient or product.
• Capacity constraints for each resource.
• Quality or proportion constraints for blends.
• A SUMPRODUCT objective function.
• Nonnegativity constraints.

If any constraint references the wrong cells—or if proportions are mis‑specified—Solver will return infeasible or incorrect results.

2. Common Blending and Production Model Errors

2.1 Wrong SUMPRODUCT Ranges

The objective or constraints reference the wrong rows or columns.

2.2 Missing Capacity Constraints

If a resource limit is missing, the model may become unbounded or unrealistic.

2.3 Incorrect Proportion or Quality Constraints

Blending models often require weighted averages. A common mistake is forgetting to divide by total output.

2.4 Decision Variables Contain Formulas

Decision variables must be numbers only. Formulas cause Solver to fail.

2.5 Negative Values Allowed by Accident

Without nonnegativity constraints, Solver may push variables negative to satisfy proportions.

3. How to Build Correct Blending and Production Constraints

3.1 Step 1 — Define Decision Variables

Example: A blend uses three ingredients:

x1 = amount of Ingredient 1
x2 = amount of Ingredient 2
x3 = amount of Ingredient 3
  

3.2 Step 2 — Add Capacity Constraints

Example: \[ x_1 \le 50,\quad x_2 \le 40,\quad x_3 \le 30 \]

3.3 Step 3 — Add Total Production Constraint

If total output must equal 100: \[ x_1 + x_2 + x_3 = 100 \]

3.4 Step 4 — Add Quality or Proportion Constraints

Example: Average purity must be at least 85%.

If purities are 90%, 80%, 70%, then: \[ \frac{90x_1 + 80x_2 + 70x_3}{x_1 + x_2 + x_3} \ge 85 \]

Multiply both sides to avoid division: \[ 90x_1 + 80x_2 + 70x_3 \ge 85(x_1 + x_2 + x_3) \]

3.5 Step 5 — Build the Objective Function

If costs are 5, 4, and 3:

Objective cell: =SUMPRODUCT(B4:D4, B10:D10)

3.6 Step 6 — Add Nonnegativity

In Solver:
x₁ ≥ 0 x₂ ≥ 0 x₃ ≥ 0

4. Numerical Example: Fixing a Broken Blending Model

A company blends three oils to produce 100 units of a final product. Solver reports infeasible because the quality constraint and SUMPRODUCT ranges were incorrect.

4.1 Incorrect Setup

• Total production constraint uses =SUM(B4:C4) instead of =SUM(B4:D4).
• Quality constraint divides by the wrong total.
• Decision variables contain formulas.
• Objective references wrong cost row.

5. Fixing the Blending Model

5.1 Correct Total Production Constraint

\[ x_1 + x_2 + x_3 = 100 \]

Excel: =SUM(B4:D4)

5.2 Correct Quality Constraint

\[ 90x_1 + 80x_2 + 70x_3 \ge 85(x_1 + x_2 + x_3) \]

5.3 Correct Objective

=SUMPRODUCT(B4:D4, B10:D10)

5.4 Add Nonnegativity

x₁ ≥ 0, x₂ ≥ 0, x₃ ≥ 0

6. Solving the Corrected Model

After fixing the constraints, Solver finds a feasible blend. Example:

• x₁ = 40
• x₂ = 30
• x₃ = 30

Quality: \[ \frac{90(40) + 80(30) + 70(30)}{100} = 85 \]

The model now satisfies all constraints and minimizes cost.

7. Additional Tips for Reliable Blending and Production Models

• Always multiply out quality constraints to avoid division.
• Ensure SUM ranges match the number of ingredients.
• Use SUMPRODUCT for cost or profit objectives.
• Keep decision variables in a clean, contiguous block.
• Use Simplex LP for linear blending models.

For more Solver troubleshooting: