Binary variables are used in Excel Solver to model yes/no decisions, on/off switches, assignments, selections, and logical constraints. Solver errors occur when binary cells are mis‑defined, contain formulas, are not contiguous, or are not properly declared as binary in the Solver dialog. This page explains how to correctly define binary variables, how to fix common mistakes, and includes multiple numerical examples.

1. What binary decision variables must look like

A valid binary variable setup requires:

• Each binary cell contains a number only (0 or 1), not a formula.
• All binary variables are in a contiguous block (e.g., B4:E4 or B4:E7).
• Every constraint and the objective reference these cells directly.
• Solver is explicitly told these cells are binary.

If any binary cell contains text, formulas, or is outside the declared range, Solver will fail or produce fractional values.

2. Common binary variable definition errors

2.1 Binary cells contain formulas

Binary cells must be editable numbers. If a cell contains =something, Solver cannot change it.

2.2 Binary range is not contiguous

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

2.3 Solver not told the variables are binary

If you forget to add the binary constraint, Solver will treat them as continuous and return fractional values.

2.4 Wrong cell references in constraints

If constraints reference the wrong rows or columns, Solver may force impossible combinations.

2.5 Binary variables used in nonlinear formulas

Expressions like =B4*C4 create nonlinearities that Simplex LP cannot solve.

3. How to correctly define binary variables

3.1 Step 1 — Create a clean binary variable block

        Option1   Option2   Option3
Choose     x1        x2        x3

Cells B4:D4 must contain numbers (0 is fine).

3.2 Step 2 — Reference these cells in the objective

Example objective: \[ \text{Maximize } Z = 8x_1 + 5x_2 + 6x_3 \] Excel: =SUMPRODUCT(B4:D4, B6:D6)

3.3 Step 3 — Add logical or capacity constraints

Example: choose at most 2 options: \[ x_1 + x_2 + x_3 \le 2 \] Excel: =SUM(B4:D4)

3.4 Step 4 — Tell Solver these cells are binary

In Solver → Add constraint → B4:D4 = binary.

4. Numerical example 1 — Select projects with a budget limit

A company can choose from 3 projects. Each project has a cost and profit. You must choose a combination that fits the budget.

4.1 Decision variables

x1 = 1 if Project 1 is selected, else 0
x2 = 1 if Project 2 is selected, else 0
x3 = 1 if Project 3 is selected, else 0

4.2 Data

        P1   P2   P3
Profit   8    5    6
Cost     7    4    3
Budget = 10

4.3 Objective

Maximize: \[ Z = 8x_1 + 5x_2 + 6x_3 \] Excel: =SUMPRODUCT(B4:D4, B6:D6)

4.4 Constraint

Budget: \[ 7x_1 + 4x_2 + 3x_3 \le 10 \] Excel: =SUMPRODUCT(B4:D4, B7:D7)

4.5 Binary constraint

B4:D4 = binary

4.6 Solution

Solver returns:

• \(x_1 = 1\)
• \(x_2 = 0\)
• \(x_3 = 1\)

Total cost = 7 + 3 = 10 (fits budget) Total profit = 8 + 6 = 14 (optimal)

5. Numerical example 2 — Binary “either/or” constraint

A factory can run Machine A or Machine B, but not both.

5.1 Decision variables

xA = 1 if Machine A is used
xB = 1 if Machine B is used

5.2 Either/or constraint

\[ x_A + x_B = 1 \] Excel: =B4 + C4

5.3 Objective

Minimize cost: \[ Z = 500x_A + 300x_B \] Excel: =500*B4 + 300*C4

5.4 Binary constraint

B4:C4 = binary

5.5 Solution

Solver chooses Machine B:

• \(x_A = 0\)
• \(x_B = 1\)

6. Numerical example 3 — Binary “at least one” constraint

A safety system requires at least one of three backup components to be active.

6.1 Constraint

\[ x_1 + x_2 + x_3 \ge 1 \] Excel: =SUM(B4:D4)

6.2 Binary constraint

B4:D4 = binary

Solver will choose the cheapest or most profitable option depending on the objective.

7. Checklist for fixing binary variable errors

• Ensure binary cells contain numbers, not formulas.
• Use a single contiguous block for all binary variables.
• Verify every constraint references the correct binary cells.
• Use SUM or SUMPRODUCT for all logical constraints.
• Explicitly set the binary constraint in Solver.
• Use Simplex LP for linear binary models.