Excel Solver: find the best answer with several inputs and limits

Goal Seek changes one input to hit one target. Solver changes several inputs to make a result as big or small as possible, while obeying limits.

Turn it on (once)

File › Options › Add-ins › Manage: Excel Add-ins › Go › tick Solver Add-in › OK. It appears at Data › Solver.

Example: how many chairs and tables to make?

Set up the sheet:

• Total profit in B7: =SUMPRODUCT(B2:C2,B3:C3)
• Wood used in B8: =SUMPRODUCT(B2:C2,B4:C4) — limit 400 kg
• Hours used in B9: =SUMPRODUCT(B2:C2,B5:C5) — limit 120 hours

Run Solver

1. Set Objective: B7. To: Max.
2. By Changing Variable Cells: B2:C2.
3. Add constraints: B8 <= 400 · B9 <= 120 · B2:C2 = int (whole units).
4. Tick Make Unconstrained Variables Non-Negative. Method: Simplex LP (the maths here is straight-line).
5. Solve › Keep Solver Solution.

Solver fills in the unit numbers that give the highest profit within the wood and hour limits.

Other jobs

• Cheapest mix of suppliers that meets demand.
• Staff rota that covers every shift with the fewest people.
• Which invoices add up to a payment amount (binary variables).

“Solver could not find a feasible solution”

The limits contradict each other, or a constraint uses the wrong sign. Relax one limit and try again. Check that the objective really depends on the changing cells.

Simpler what-if tools: What-If Analysis