Working Backward From the Answer: Goal Seek in Piskes Sheets

Most spreadsheet work starts with known inputs and asks what they produce. Goal Seek runs the question in reverse: you know the result you want, and you need to find the single input that gets you there. What sales volume hits break-even? What monthly contribution reaches a savings target? What score on the final gets you to a B average? These are all the same shape of problem — one formula, one variable you can change, one target value — and Piskes Sheets solves it directly instead of making you guess-and-check.

Setting it up

Goal Seek needs three things from you: a cell containing a formula (the result), a target value for that formula to reach, and a cell it's allowed to change to get there. Point it at your break-even formula, tell it you want the result to equal zero, and name the sales-volume cell as the one it can adjust. Piskes runs the search and writes the answer back into that cell.

Why this isn't just "Excel's Goal Seek, but in a browser"

In desktop Excel, Goal Seek can lean on the spreadsheet engine's own synchronous recalculation — change a cell, read the result, repeat, as fast as the engine can go. Piskes Sheets runs on Univer, and Univer recalculates asynchronously. A solver that assumes it can read a fresh result the instant it writes a value would be reading stale numbers.

So the solver in frontend/apps/drive/src/lib/office/what-if.ts is built as a pure function: it takes a callback for reading a cell's current value rather than reaching into the workbook directly, and it doesn't assume anything about when that value becomes accurate beyond what the callback promises. That separation is what makes it safe to run against an async recalculation engine at all.

The method itself is the secant method — it uses two trial points and the slope between them to jump toward the target, which converges faster than a plain bisection search for most well-behaved formulas — with a fall back to bisection when the secant method's assumptions don't hold (a flat region, a formula that isn't smooth). You don't need to know which method ran; you'll just notice Goal Seek reliably lands on an answer.

What it doesn't try to do

Goal Seek in Piskes Sheets solves for exactly one changing cell against exactly one target. It's deliberately scoped that way — full scenario management, multi-variable optimization, or Monte Carlo-style simulation aren't part of this feature, and the code that implements it says so directly in its own header comments. If your question is "what combination of three inputs gets me closest to this target," that's a different, heavier tool than what Goal Seek is built to be. For the single-variable, single-target question — which covers most of the break-even and target-setting questions people actually ask a spreadsheet — it's a fast, precise answer instead of a manual trial-and-error loop.