goalseek

One-dimensional goal seek over a Grid: find the value of a variable cell that drives a formula cell to a target. See the Goal seek guide for the :goal command.

goalseek

One-dimensional goal-seek over a Grid.

Find a value for a variable cell such that a formula cell evaluates to a given target value. Useful for spreadsheet what-if of the form "what input makes this output equal X?" -- the most common form of Solver use in Excel and the only one that doesn't need an LP.

Algorithm: bisection with an auto-bracket pre-step. Bisection is slow asymptotically but at spreadsheet scale a few dozen recalcs run in a few milliseconds; correctness, simplicity, and graceful failure on non-monotonic or noisy formulas are worth more than the iteration count. Brent's method would converge faster on smooth f but adds edge cases (oscillation, the mflag dance, slow-progress detection) that aren't justified here.

The variable cell must hold a value, not a formula (analogous to decision cells in opt.py): goal-seek will overwrite it on success, and apply=False restores it. The formula cell must contain a formula; it should depend on the variable cell (otherwise f doesn't change with x and bracketing fails).

SeekResult dataclass

SeekResult(
    converged: bool,
    iterations: int,
    var_value: float,
    formula_value: float,
    residual: float,
    applied: bool,
)

GoalSeekError

Bases: Exception

User-facing failure: bad cell selection, no sign change, non-converged.

seek

seek(
    grid: Grid,
    formula_cell: CellKey,
    target: float,
    var_cell: CellKey,
    *,
    lo: float | None = None,
    hi: float | None = None,
    tol: float = 1e-09,
    max_iter: int = 100,
    apply: bool = True,
) -> SeekResult

Adjust var_cell so that formula_cell evaluates to target.

If both lo and hi are given they're used as the search bracket; otherwise the algorithm auto-brackets outward from the variable cell's current value.

tol is relative: bisection stops when the bracket is within tol of |x|, and the result is converged when the residual is within 1e3 * tol * max(1, |target|).

On success (residual within tolerance) the variable cell is left holding the solved value and the rest of the grid is recalculated to reflect it; callers can roll back via undo (the TUI wrapper records a grid snapshot). apply=False runs the search without leaving the grid mutated.

Source code in src/gridcalc/goalseek.py

def seek(
    grid: Grid,
    formula_cell: CellKey,
    target: float,
    var_cell: CellKey,
    *,
    lo: float | None = None,
    hi: float | None = None,
    tol: float = 1e-9,
    max_iter: int = 100,
    apply: bool = True,
) -> SeekResult:
    """Adjust ``var_cell`` so that ``formula_cell`` evaluates to ``target``.

    If both ``lo`` and ``hi`` are given they're used as the search bracket;
    otherwise the algorithm auto-brackets outward from the variable cell's
    current value.

    ``tol`` is relative: bisection stops when the bracket is within ``tol`` of
    ``|x|``, and the result is converged when the residual is within
    ``1e3 * tol * max(1, |target|)``.

    On success (residual within tolerance) the variable cell is left holding
    the solved value and the rest of the grid is recalculated to reflect it;
    callers can roll back via undo (the TUI wrapper records a grid snapshot).
    ``apply=False`` runs the search without leaving the grid mutated.
    """
    fc, fr = formula_cell
    vc, vr = var_cell
    fcell = grid.cells[fc][fr]
    vcell = grid.cells[vc][vr]

    if fcell.type != FORMULA:
        raise GoalSeekError("target cell must contain a formula")
    if vcell.type == FORMULA:
        raise GoalSeekError(
            "variable cell must hold a value (not a formula); "
            "goal-seek would otherwise overwrite a live computation"
        )
    if vcell.type not in (EMPTY, NUM):
        raise GoalSeekError("variable cell must be numeric or empty")
    if not all(math.isfinite(v) for v in (target, lo or 0.0, hi or 0.0)):
        raise GoalSeekError("target and bracket must be finite numbers")

    # An empty variable cell is the shared read-only placeholder until created.
    created = grid.cell(vc, vr) is None
    vcell = grid._ensure_cell(vc, vr)

    orig_type = vcell.type
    orig_val = vcell.val

    def restore() -> None:
        vcell.type = orig_type
        vcell.val = orig_val
        if created:
            grid._cells.pop((vc, vr), None)
        grid.recalc()

    def f(x: float) -> float:
        """Set var to x, recalc, return formula_value - target."""
        vcell.type = NUM
        vcell.val = x
        # Both cells are on the active sheet, so only the variable's dependants
        # need recomputing; recalc falls back to a full pass without a graph.
        grid.recalc({(vc, vr)})
        v = fcell.val
        if not isinstance(v, (int, float)) or v != v:  # NaN-safe
            raise GoalSeekError(
                f"target cell evaluated to non-numeric at var={x!r}; "
                "ensure the formula depends only on numeric inputs"
            )
        return float(v) - target

    try:
        x_start = float(orig_val) if orig_type == NUM else 0.0
        if lo is not None and hi is not None:
            if lo >= hi:
                raise GoalSeekError(f"bracket is empty: lo={lo} >= hi={hi}")
            x_solved, iters = _bisect(f, lo, hi, tol, max_iter, target)
        elif f(x_start) == 0.0:
            # Already at a root; a repeated one has no sign change to bracket.
            x_solved, iters = x_start, 0
        else:
            a, b = _auto_bracket(f, x_start)
            x_solved, iters = _bisect(f, a, b, tol, max_iter, target)
        # Re-evaluate at the solution to capture the final formula value
        # and leave the grid in the solved state (or restore below).
        residual = f(x_solved)
        formula_value = residual + target
        converged = abs(residual) <= 1e3 * tol * max(1.0, abs(target))
        applied = False

        if apply and converged:
            applied = True  # grid is already in the solved state
            vcell.text = number_text(x_solved)
        else:
            restore()

        return SeekResult(
            converged=converged,
            iterations=iters,
            var_value=x_solved,
            formula_value=formula_value,
            residual=residual,
            applied=applied,
        )
    except Exception:
        # Any failure path leaves the grid as it was before the search.
        restore()
        raise