What Solver does in a spreadsheet model
A Solver model has three parts: an objective cell containing a formula, one or more changing cells representing decisions, and constraints that describe what is allowed. Sheet Analyst changes the decision values, recalculates the workbook model and reports a feasible solution for you to review.
| Part | What to select | Example |
|---|---|---|
| Objective | A single formula cell to maximize, minimize or set to a target | Total profit or delivery cost. |
| Changing cells | Input cells Solver is allowed to adjust | Units produced by each product line. |
| Constraints | Limits or relationships that the solution must satisfy | Capacity, budget, demand or integer requirements. |
How to set up a Solver run
Build and check the formulas in the sheet first. A small, well-bounded model is easier to troubleshoot and usually responds faster than a workbook with many volatile or external formulas.
- Choose one objective formula and select whether to maximize, minimize or reach a target.
- Select the decision cells Solver can change; use numeric starting values.
- Add constraints, such as a resource limit, a minimum requirement or integer decisions.
- Run the search, review the status and answer report, then keep or restore the proposed values.
Examples of useful Solver models
Solver is useful when you need to choose among many combinations rather than adjust one input by hand.
- Maximize contribution margin while staying within labor and material capacity.
- Minimize a delivery or purchasing cost while meeting demand.
- Allocate a fixed budget across projects subject to minimum and maximum allocations.
- Choose whole-number quantities when fractional decisions do not make sense.
Know what the result means
A Solver result is a candidate solution to the model you supplied—not a validation of the model’s assumptions. Sheet Analyst uses a deterministic linear/mixed-integer path for recognized linear models and a derivative-free search for other supported models. Nonlinear search is heuristic, so it may find a useful feasible solution without proving that it is the global optimum. Check the constraints, formulas and business interpretation before relying on a result.
Common questions
Do I need to write code to use Sheet Analyst Solver?
No. Set up the model with spreadsheet formulas, then choose the objective cell, changing cells and constraints in the guided Solver workflow.
Does Solver overwrite my spreadsheet?
Solver temporarily writes candidate values into the selected changing cells so the model can recalculate; after the run, those cells can contain the best candidate it found. Review the values and use Restore if you want to return to the original inputs. Keep a copy of an important model before solving.
Does nonlinear Solver guarantee the best possible answer?
No. The nonlinear search is heuristic and does not prove a global optimum. Verify feasibility, consider alternative starting values and independently validate important decisions.