The first thing most people do when they need a binary decision in Solver is open the Add Constraint dialog, type bin manually into the dropdown or value field, and click OK. That’s almost always wrong — because Solver ignores that entry unless you’ve selected the right cells *before* opening the dialog, and even then, it silently fails if any cell contains text or formatting. You’ll get no error — just a suboptimal solution.
The Setup
We’re optimizing vendor selection for Alibaba’s internal procurement team. Sarah Chen (Procurement Lead) needs to pick exactly 4 out of 9 vendors to supply safety gear across 3 regional warehouses. Each vendor has capacity limits, unit cost, and reliability score. The goal: minimize total cost while meeting demand and keeping average reliability ≥ 87%.
| Vendor | Unit Cost ($) | Capacity (units) | Reliability (%) | Selected? |
|---|---|---|---|---|
| Acme Corp | 24.50 | 1,200 | 92 | 0 |
| Vertex Safety | 28.10 | 950 | 89 | 0 |
| NexGuard Ltd | 22.75 | 1,400 | 84 | 0 |
| SafeLine Inc | 31.20 | 780 | 95 | 0 |
| TerraShield | 26.40 | 1,050 | 86 | 0 |
| OmniHelm Group | 29.90 | 820 | 91 | 0 |
| CorePPE Co | 23.80 | 1,300 | 83 | 0 |
| VeriGuard Pro | 33.60 | 690 | 94 | 0 |
| StrataSafety | 27.20 | 1,100 | 88 | 0 |
The binary decision column is E2:E10. Right now, all values are zero — but we want Solver to flip exactly four of them to 1, not decimals or fractions.
The Challenge
Adding binary constraints isn’t about typing bin. It’s about telling Solver: “These cells must be integers AND must equal zero or one — no rounding, no tolerance.” Most users miss that Solver treats bin as a *constraint type*, not a value. If you select E2:E10, click Add Constraint, and type bin in the ‘Constraint’ box — Solver ignores it entirely. The correct method requires selecting cells *first*, then choosing ‘bin’ from the dropdown *before* entering anything else. And here’s the counterintuitive part: you must apply the binary constraint *after* adding integer constraints — otherwise, Solver may violate integrality during intermediate iterations.
Also, if any cell in E2:E10 contains text (like “Yes”), a date, or even a number formatted as percentage, Solver won’t recognize it as eligible for binary treatment — and will return “Solver could not find a feasible solution” without explaining why.
Walking Through It
Open Solver (Alt+A+Y+S). Set Objective to $F$1 (total cost, calculated as =SUMPRODUCT(B2:B10,E2:E10)). Choose ‘Min’. By Changing Variable Cells: E2:E10.
Step 1: Click ‘Add’. In the Add Constraint dialog, select E2:E10 in the Cell Reference box — don’t type it. Then click the middle dropdown and choose ‘int’ (not ‘bin’ yet). Click OK.
Step 2: Click ‘Add’ again. Select E2:E10 again. Now choose ‘bin’ from the same dropdown. Click OK. Yes — you need both. The ‘int’ ensures whole numbers; ‘bin’ locks them to 0 or 1.
Step 3: Add demand constraint: F12 >= 3500 (total units supplied must meet warehouse demand). And reliability: F13 >= 87 (calculated as =SUMPRODUCT(D2:D10,E2:E10)/SUM(E2:E10)).
Before solving, check ‘Make Unconstrained Variables Non-Negative’ is unchecked — because our binary variables are fine at zero.
| Vendor | Selected? (Before) | Selected? (After Solver) |
|---|---|---|
| Acme Corp | 0 | 1 |
| Vertex Safety | 0 | 1 |
| NexGuard Ltd | 0 | 0 |
| SafeLine Inc | 0 | 1 |
| TerraShield | 0 | 0 |
| OmniHelm Group | 0 | 1 |
| CorePPE Co | 0 | 0 |
| VeriGuard Pro | 0 | 0 |
| StrataSafety | 0 | 0 |
Notice how NexGuard, TerraShield, CorePPE, VeriGuard, and StrataSafety stayed at zero — not because they’re disqualified, but because their cost/reliability ratio didn’t make the cut within the 4-vendor limit. The beauty of this approach is Solver respects the hard ceiling: no fractional selections, no hidden rounding, and no false positives from misapplied constraints.
The Result
Final solution selects Acme Corp, Vertex Safety, SafeLine Inc, and OmniHelm Group — total cost $2,842.10, average reliability 91.5%, capacity met at 4,120 units.
| Vendor | Unit Cost ($) | Reliability (%) | Selected |
|---|---|---|---|
| Acme Corp | 24.50 | 92 | ✓ |
| Vertex Safety | 28.10 | 89 | ✓ |
| NexGuard Ltd | 22.75 | 84 | — |
| SafeLine Inc | 31.20 | 95 | ✓ |
| TerraShield | 26.40 | 86 | — |
| OmniHelm Group | 29.90 | 91 | ✓ |
| CorePPE Co | 23.80 | 83 | — |
| VeriGuard Pro | 33.60 | 94 | — |
| StrataSafety | 27.20 | 88 | — |
| Total | $2,842.10 | 91.5% | 4 vendors |
What Could Go Wrong
Mistake #1: Applying ‘bin’ before ‘int’
Solver may return non-integer values like 0.9999999 or -0.0000001 in E2:E10. Why? Because ‘bin’ alone doesn’t enforce integrality — it assumes you’ve already constrained the variable to integers. Without the prior ‘int’ step, Solver uses relaxed linear programming internally and only rounds at the end — which breaks feasibility.
Mistake #2: Including blank cells in the range
If E7 is empty (not 0), Solver treats it as undefined — and throws “Problem is too large” even with 9 variables. Always pre-fill binary columns with 0s using =IF(ISBLANK(E2),0,E2) or paste-special-values zeros before launching Solver.
Mistake #3: Using merged cells anywhere in the model
Even if merged cells aren’t in E2:E10 — say, a merged header in row 1 — Solver fails silently with “Solver encountered an error” and no details. Unmerge everything. Always.
Next step: Save your Solver model (Load/Save button → Save Model) to SolverModel.bin in your project folder. That way, you can reload constraints in under 3 seconds next time — no retyping, no dropdown hunting.
