Why does Solver return #N/A when your formula references a named range? Why does it converge on $0 every time—even when you know the answer is $12,480? Why does it work perfectly on your test sheet but crash on the real budget model?
Quick Answer
Excel Solver doesn’t ‘solve’ like a math student—it iteratively adjusts decision variables to minimize or maximize an objective while respecting constraints, using numerical methods (GRG Nonlinear, Simplex LP, or Evolutionary) that depend entirely on your starting values, constraint logic, and model structure—not just your formulas.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| GRG Nonlinear | Set objective → Choose variable cells → Add ≤/≥/= constraints → Select GRG → Solve | Smooth, continuous models (e.g., portfolio optimization) | Fails with discontinuities (IF, ABS, VLOOKUP); sensitive to initial guesses |
| Simplex LP | Same setup, but ensure all relationships are linear → Select Simplex LP → Solve | Linear resource allocation (e.g., production mix, staffing schedules) | Cannot handle IF, MAX, MIN, or nonlinear functions—even if they appear harmless |
| Evolutionary | Enable Evolutionary engine → Increase iterations (default 100 too low) → Use integer/binary constraints freely | Scheduling, sequencing, or models with logical functions (IF, CHOOSE, COUNTIF) | Slow; no guarantee of global optimum; requires tighter bounds on variables |
| Solver Table + GRG | Run Solver multiple times via macro or manual iteration across a parameter (e.g., discount rate in B1) | Sensitivity analysis—see how optimal profit shifts as input changes | Not built-in; requires VBA or copy-paste discipline; no native charting |
Method 1 Deep Dive
Let’s optimize quarterly marketing spend across four channels for Acme Corp. Goal: Maximize lead volume (cell D12), constrained by total budget ($75,000) and minimum spend per channel ($5,000).
Setup: A1:B5 contains channel names and current spend. C1:C5 holds cost-per-lead estimates: Search ($18.20), Email ($4.10), LinkedIn ($27.60), Referral ($1.95). D1:D5 calculates leads = Spend / Cost-per-Lead. D12 sums D1:D5. Budget constraint: SUM(A2:A5) ≤ 75000. Minimums: A2:A5 ≥ 5000.
Here’s the counterintuitive part: If you leave A2:A5 blank before running GRG Nonlinear, Solver often lands on $5,000 for all channels—and stops. Why? It treats blanks as zero, then hits the lower bound immediately and declares convergence. The fix? Pre-fill A2:A5 with realistic guesses: $22,000, $18,500, $14,000, $20,500. Now GRG explores meaningfully.
Use Alt+A+Y+S to open Solver. Set Objective: D12 → To: Max. By Changing Variable Cells: A2:A5. Add constraints: A2:A5 ≥ 5000, SUM(A2:A5) ≤ 75000. Select GRG Nonlinear. Click Solve. Result: $31,200 → Search, $8,900 → Email, $0 → LinkedIn (too expensive), $24,900 → Referral. Total leads: 15,822.
Method 2 Deep Dive
Now try assigning 7 sales reps to 7 client accounts—each rep gets exactly one account, each account gets one rep—with travel time as the objective to minimize. This is classic assignment, but with non-linear data: Rep Sarah Chen (B2) takes 42 min to reach Acme Corp (C2), but 87 min to reach Zenith Labs (C3)—and those times aren’t linearly related.
Build a 7×7 matrix in B2:H8. Row headers = reps (Sarah Chen, Rajiv Mehta, Lena Torres…). Column headers = clients (Acme Corp, Zenith Labs, Novo Dynamics…). Values = minutes (e.g., B2=42, C2=87, B3=63, etc.). In I2:I8, sum each rep’s assigned time using SUMPRODUCT. In B9:H9, sum each client’s assigned time. Objective cell J2 = SUM(I2:I8).
You need binary decision variables: 1 = assigned, 0 = not. So add a parallel 7×7 grid in B12:H18, all initially 0. Then set constraints: SUM(B12:H12)=1 (Sarah gets one client), SUM(B12:B18)=1 (Acme gets one rep), and B12:H18 = bin. That last one is critical—and only Evolutionary handles it reliably.
Open Solver (Alt+A+Y+S). Set Objective: J2 → Min. By Changing: B12:H18. Add constraints: B12:H18 = bin, SUM(B12:H12)=1, SUM(B12:B18)=1, … (repeat for all rows/columns). Select Evolutionary. Under Options, raise Maximum Time to 300 sec and Iterations to 5000. Click Solve. You’ll get a clean 1:1 assignment—no overlaps, no gaps.
Cheat Sheet
| Action | Shortcut / Step | Notes |
|---|---|---|
| Open Solver | Alt+A+Y+S | Only works if Solver Add-in is enabled (File > Options > Add-ins > Manage Excel Add-ins > Go…) |
| Force GRG to explore | Pre-fill variable cells with mid-range values (not zeros or blanks) | Blank cells = zero → GRG assumes local optimum at bound. Try 50% of budget or average historical value. |
| Add binary constraint | In Solver dialog: Add → Cell Reference → select range → Operator: 'bin' | Don’t type 'bin' manually—use the dropdown. Only Evolutionary and Simplex accept this. |
| Check feasibility first | Use Formula Auditing > Evaluate Formula on constraint cells (e.g., =SUM(A2:A5)≤75000) | If any constraint returns FALSE with current inputs, Solver will fail before it even starts. |
| Save model | Solver dialog → Load/Save → select empty range (e.g., Z1:Z10) | Lets you swap models without re-entering 12 constraints. Saved models are plain text—copyable between workbooks. |
