📎 This article includes 1 downloadable practice file ↓
Goal Seek changes one cell to hit one target. Real decisions have more moving parts: how many of each product to make with limited machine hours, how to split an ad budget across channels, how many staff to roster per shift. Solver handles these. You tell it what to maximise or minimise, which cells it may change, and the limits it must respect, and it searches for the best combination.
In this article
Turn Solver on (once)
File › Options › Add-ins › Manage: Excel Add-ins › Go, tick Solver Add-in. It appears at the right end of the Data tab.
Step 1: build the model as formulas
Solver only works on a model that already calculates. Example: a boutique makes kurtas, sarees, dupattas and suit sets.
| Product | Profit/unit | Machine hrs | Labour hrs | Max demand | Plan |
|---|---|---|---|---|---|
| Kurta | 180 | 2 | 3 | 120 | 60 |
| Saree | 420 | 5 | 4 | 60 | 30 |
| Dupatta | 90 | 1 | 1 | 200 | 100 |
| Suit set | 300 | 3 | 5 | 80 | 40 |
Limits: 500 machine hours and 600 labour hours a month. The Plan column (F2:F5) holds the cells Solver will change. Add the outputs:
Total profit: =SUMPRODUCT(B2:B5, F2:F5)
Machine hrs used: =SUMPRODUCT(C2:C5, F2:F5)
Labour hrs used: =SUMPRODUCT(D2:D5, F2:F5)The starting plan earns ₹44,400 and uses 490 machine hours and all 600 labour hours. Can we do better?
Step 2: set up Solver
- Set Objective: the total profit cell. Choose Max.
- By Changing Variable Cells: F2:F5.
- Subject to the Constraints (Add each):
- Machine hrs used <= 500
- Labour hrs used <= 600
- F2:F5 <= E2:E5 (demand)
- F2:F5 = integer (you can’t sell half a saree)
- Tick Make Unconstrained Variables Non-Negative.
- Select a Solving Method: Simplex LP, because profit and hours are straight multiplications.
Click Solve, choose Keep Solver Solution, and tick Answer and Sensitivity reports if offered.
Choosing the method
| Method | Use when |
|---|---|
| Simplex LP | Everything is linear: SUMPRODUCT, SUM, quantity × rate. Fast, and finds the true best answer. |
| GRG Nonlinear | Smooth curves: prices that change with volume, squares, ratios. May stop at a “local” best; try different starting values. |
| Evolutionary | Lookups, IF, rounding, anything jumpy. Slow, gives a good answer rather than a proven best. |
Reading the result
The Answer report lists each constraint as Binding (fully used) or Not Binding (spare). A binding limit is your bottleneck; in the starting plan that’s labour, since all 600 hours are used. The Sensitivity report (Simplex only, without integer constraints) shows the shadow price, meaning how much profit one more labour hour would add. That tells you whether overtime pays for itself.
Other common uses
- Ad budget: maximise leads across channels with a total budget and minimum spend per channel.
- Staff roster: minimise staff count with at least N people each hour (binary or integer variables).
- Which invoices add up to this payment? Binary changing cells (0/1) next to each invoice, constraint SUMPRODUCT = payment amount.
- Cutting stock: minimise waste when cutting fabric rolls into pieces.
Where people go wrong
| Problem | Cause |
|---|---|
| “Solver could not find a feasible solution” | Constraints contradict each other (e.g. minimum orders need more hours than exist) |
| Answer has fractions | Missing the integer constraint |
| Different answer each run | Nonlinear model with GRG; start from a sensible plan |
| “The linearity conditions are not satisfied” | IF or lookups in the model with Simplex LP; use Evolutionary or rewrite linearly |
| Negative quantities | Non-negative box unticked |
Practice
Download the practice workbook below. Build the model formulas first, then run Solver and see how much better than the starting plan it does. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.
Next: Goal Seek and Data Tables.
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Solver product-mix practice workbookA four-product model with machine and labour limits: build the formulas, check feasibility, then let Solver find the most profitable plan.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.