Solver

Excel-Solver-style what-if optimization — find variable-cell values that maximize, minimize, or hit a target for an objective formula.

Tools → Solver… answers "what values make this come out best?" — the same job as Excel's Solver. Give it an objective formula, name the cells it may change, optionally add constraints, and it searches for the best assignment. Nothing touches your document until you accept the result.

Setting up a problem

• Set objective — any formula: Plan.Profit:1, SUM(Plan.Profit), or a full expression. It must evaluate to a number.
• To — Max, Min, or Value of: a specific target number.
• By changing variable cells — one cell per line (or comma-separated), written as Table.Column:Row with a 1-based row: Plan.Price:1. Quoted names work as they do in formulas: 'Q1 Plan'.'Unit Price':1.
• Subject to constraints — optional, one per line, in the form lhs <= rhs, lhs >= rhs, or lhs = rhs. Both sides are formulas (a bare number counts): Plan.Units:1 <= 500, SUM(Plan.Cost) <= Budget.

Press Solve. The search runs against a private trial copy of the document, so you can watch the result appear without your tables changing. The dialog then shows the objective it reached and the value found for each variable cell — and tells you plainly if it couldn't fully satisfy the constraints, and by how much.

Keep Solution writes the found values into the variable cells as ordinary edits — one undoable edit per cell, so Ctrl+Z walks them back. Close discards everything.

Example

Maximize Plan.Units:1 * Plan.Price:1 - Plan.Cost:1 by changing Plan.Price:1 and Plan.Units:1, subject to Plan.Units:1 <= 500 and Plan.Price:1 >= 0.

What it can and can't do

• Variable cells must be data cells. A calc column's value is its formula — the solver refuses it and tells you so. Point it at the inputs the formula reads instead.
• Everything is numeric. The objective and both sides of every constraint must evaluate to numbers; variables are treated as continuous numbers.
• It uses a numeric search (Nelder-Mead with constraint penalties) — the right tool for smooth problems with a handful of variables, like Excel's default GRG engine. There is no integer/binary constraint support and no dedicated linear-programming or evolutionary engine.
• Constraints are enforced by penalty, so a hard problem can end at "best found, constraints not fully satisfied" — the dialog reports the miss rather than pretending.
• The search is bounded (a few thousand formula evaluations), so it returns quickly; a poor starting point can matter on lumpy problems. Seeding the variable cells with a reasonable guess before solving helps.