Most Excel trainers treat Solver like a black box you feed numbers into and pray for an answer. They’re wrong. Solver doesn’t ‘solve’ — it searches. And if your model has even one unbounded variable or a circular reference buried in a named range, it won’t tell you. It’ll just return ‘Solver found a solution’ while quietly ignoring half your constraints.
The Setup
You’re managing procurement for three regional offices: Beijing, Dubai, and São Paulo. Each office orders raw materials from four suppliers — all with different unit costs, minimum order thresholds, and weekly capacity limits. Your job is to meet demand at lowest total cost — but you can’t order partial pallets, and no supplier can ship more than their weekly cap.
| Supplier | Unit Cost (USD) | Min Order (pallets) | Weekly Cap (pallets) | Beijing Demand | Dubai Demand | São Paulo Demand |
|---|---|---|---|---|---|---|
| Acme Corp | $42.50 | 5 | 32 | 14 | 9 | 11 |
| Tianjin Materials | $38.75 | 8 | 40 | 16 | 12 | 7 |
| Al Maktoum Trading | $45.20 | 6 | 28 | 10 | 15 | 13 |
| Rio Siderúrgica | $40.90 | 7 | 35 | 8 | 10 | 18 |
| Total Demand | — | — | — | 48 | 46 | 49 |
Data lives in A1:G6. Demand totals are in E6:G6. Unit costs sit in B2:B5. You’ll build decision variables in E2:G5 — those are the pallet quantities you’ll let Solver adjust.
The Challenge
You need to minimize total cost = SUMPRODUCT(E2:G5,B2:B5). But here’s what most miss: Solver doesn’t know your numbers represent pallets. If you don’t set integer constraints on E2:G5, it’ll happily suggest 12.37 pallets from Acme Corp. That’s useless. Also — if your total supply (sum of weekly caps) is less than total demand (48+46+49=143), Solver will return ‘Solution found’ and give you negative allocations. Yes — negative. Because it treats constraints as soft unless you flag them as ‘Binding’.
And don’t forget: your objective cell must be a single formula referencing only decision cells and constants. No INDIRECT(), no OFFSET(), no volatile functions. Solver breaks silently if it hits one.
Walking Through It
First, enable Solver: File → Options → Add-ins → Manage Excel Add-ins → Check ‘Solver Add-in’ → OK. Then press Alt+A+Y+S.
Set Objective: $H$7 (where =SUMPRODUCT(E2:G5,B2:B5) lives). To: Min. By Changing Variable Cells: E2:G5.
Now — this is where 80% fail. Click ‘Add’. Set E2:G5 >= 0. Click Add again. Set E2:G5 = integer. Click Add. Now set row totals: SUM(E2:E5) = E6, SUM(F2:F5) = F6, SUM(G2:G5) = G6. Then column caps: SUM(E2:G2) <= D2, SUM(E3:G3) <= D3, etc.
Before solving:
| Supplier | Beijing | Dubai | São Paulo |
|---|---|---|---|
| Acme Corp | 0 | 0 | 0 |
| Tianjin Materials | 0 | 0 | 0 |
| Al Maktoum Trading | 0 | 0 | 0 |
| Rio Siderúrgica | 0 | 0 | 0 |
After clicking Solve:
| Supplier | Beijing | Dubai | São Paulo |
|---|---|---|---|
| Acme Corp | 14 | 0 | 11 |
| Tianjin Materials | 16 | 12 | 0 |
| Al Maktoum Trading | 10 | 15 | 13 |
| Rio Siderúrgica | 8 | 10 | 18 |
Total cost drops from $0 to $5,834.20. All demands met. All supplier caps respected. All values integers.
The Result
| Office | Supplier Used | Pallets | Cost |
|---|---|---|---|
| Beijing | Acme Corp | 14 | $595.00 |
| Beijing | Tianjin Materials | 16 | $620.00 |
| Beijing | Al Maktoum Trading | 10 | $452.00 |
| Beijing | Rio Siderúrgica | 8 | $327.20 |
| Dubai | Tianjin Materials | 12 | $465.00 |
| Dubai | Al Maktoum Trading | 15 | $678.00 |
| Dubai | Rio Siderúrgica | 10 | $409.00 |
| São Paulo | Acme Corp | 11 | $467.50 |
| São Paulo | Al Maktoum Trading | 13 | $587.60 |
| São Paulo | Rio Siderúrgica | 18 | $736.20 |
What Could Go Wrong
Mistake #1: Using relative references in constraints. If you type ‘E2 >= 0’ instead of ‘$E$2 >= 0’, and then copy-paste that constraint across rows, Solver interprets it as ‘F2 >= 0’, ‘G2 >= 0’, etc. It won’t warn you. Your model becomes inconsistent.
Mistake #2: Forgetting to uncheck ‘Make Unconstrained Variables Non-Negative’. This checkbox is ON by default. If your problem allows zero or negative flow (e.g., returns, credits), leaving it checked forces all decision variables ≥ 0 — even if your logic says otherwise.
Mistake #3: Naming a range ‘Demand’ that includes headers. Solver reads names literally. If ‘Demand’ refers to E1:G6 (including labels), and you use it in SUM(E2:E5)=Demand, Solver compares 14+16+10+8 = E1:G6 — which throws a #VALUE! error behind the scenes. The status bar just says ‘Ready’. No error dialog. No warning.
Next step: Open your current workbook. Press Alt+A+Y+S. In the Solver Parameters dialog, click ‘Load/Save’. Select C10:C15. Click ‘Save’. Now paste this range into a new sheet — it stores your full model (objective, variables, constraints) as comma-separated values you can reload later. Do it now. Don’t wait.
