StatPakHow to use Solver in Google Sheets
Solver finds the values of several cells that maximise or minimise a target, while respecting limits you set. This is linear programming, used for product mix, staffing, blending, transport and budget problems.
Example: product mix
A workshop makes chairs (profit $3) and tables (profit $5). Chairs use 1 hour of cutting, tables use 2 hours of finishing, and both share an assembly line: 3 hours per chair, 2 per table. Available: 4 cutting hours, 12 finishing hours, 18 assembly hours.
| Cell | Contents |
|---|---|
B2, B3 | Chairs and tables to make (start at 0) |
B5 | Profit: =3*B2 + 5*B3 |
B7, B8, B9 | Hours used: =B2, =2*B3, =3*B2 + 2*B3 |
C7, C8, C9 | Hours available: 4, 12, 18 |
- Open Extensions, StatPak, Solver.
- Set objective:
B5, To: Max. - By changing variable cells:
B2:B3. - Add the constraint
B7:B9<=C7:C9. - Keep Make unconstrained variables non-negative ticked and click Solve.
Solver sets B2 to 2 chairs and B3 to 6 tables, for a profit of $36. No other mix within the limits earns more.
Integer and binary constraints
If you can only make whole units, add a constraint on B2:B3 with the relation int. For yes/no decisions (open a store or not, pick a project or not), use bin, which allows only 0 or 1.
When Solver says the model is not linear
StatPak's Solver uses the Simplex method, which requires the objective and constraints to be sums of variables times constants. Formulas such as =B2*B3, =B2^2 or =IF(B2>5, ...) make the model nonlinear. Often you can rewrite the model linearly, for example by replacing an IF with a binary variable.