How does Goal Seek find a break-even point?

Goal Seek looks like a spreadsheet feature, but it is a root finder. You give it a cell with a formula, a target value and one input cell, and it searches for the input that makes formula(input) - target equal zero. The Spreadsheet Editor in the osFoundry desktop app describes its own goal-seek command in one line: "Secant search with bisection once the answer is bracketed; stops within Maximum Change (0.001) or after Maximum Iterations (100)". This post rebuilds that description in Python, runs it on the same small break-even sheet that was used to try the app, and then on a profit curve where the answer depends on where the search starts. Everything below was run with Python 3.9 on an Apple M1 Mac. The shop figures are example data.

The sheet as a function

The test sheet has a price of $20 (B1), a cost of $8 per item (B2), monthly costs of $3,600 (B3), 200 items sold (B4), and a profit cell B5 with =B4*(B1-B2)-B3, which shows -$1,200. In the app, Goal Seek with Set cell B5, To value 0 and By changing cell B4 returned 300. A second run with To value 1200 and By changing cell B1 returned a price of $24.

For a root finder, the sheet is just a function of the one cell it is allowed to change. Every call is one recalculation of the workbook:

price, cost, fixed = 20, 8, 3600
profit_by_sold  = lambda n: n * (price - cost) - fixed   # B4 changes
profit_by_price = lambda p: 300 * (p - cost) - fixed     # B1 changes, B4 = 300

Secant steps, bisection as a safety net

The secant method takes the last two guesses, draws a straight line through their results, and jumps to where that line crosses the target. It needs no derivative, which matters for a spreadsheet: the formula is a black box, and the only thing you can do with it is evaluate it. Its weakness is that on a curve the line can point far away. Bisection is slow but cannot fail once you have two inputs whose results sit on opposite sides of the target. The hybrid uses secant steps and falls back to halving the bracket whenever the line points outside it.

def goal_seek(f, target, x0, max_iter=100, max_change=0.001):
    """Find x so that f(x) == target: secant steps, bisection once bracketed."""
    calls = 0
    def g(x):
        nonlocal calls
        calls += 1
        return f(x) - target              # one call = one recalculation of the sheet

    a, ga = x0, g(x0)
    if abs(ga) <= max_change:
        return x0, 0, calls, True
    b = x0 * 1.01 if x0 else 0.01         # second guess, 1% away from the start
    gb = g(b)
    br = sorted([(a, ga), (b, gb)]) if ga * gb < 0 else None   # [(lo, g), (hi, g)]
    for it in range(1, max_iter + 1):
        if gb == ga:                      # flat: the secant has no slope
            return b, it, calls, False
        x = b - gb * (b - a) / (gb - ga)  # where the line through a, b hits zero
        if br and not br[0][0] < x < br[1][0]:
            x = (br[0][0] + br[1][0]) / 2  # line points outside: bisect instead
        gx = g(x)
        if abs(gx) <= max_change:
            return x, it, calls, True
        if br:                            # keep the half that still has the sign change
            br = [br[0], (x, gx)] if br[0][1] * gx < 0 else [(x, gx), br[1]]
        elif gb * gx < 0:
            br = sorted([(b, gb), (x, gx)])
        a, ga, b, gb = b, gb, x, gx
    return b, max_iter, calls, False

Three choices here are mine, not taken from the app: the second guess is 1% away from the starting value, Maximum Change is read as "the result is within 0.001 of the target", and the function returns converged = False instead of raising when it runs out of iterations. The return value mirrors what the editor's command reports: value, iterations and whether it converged.

On a straight line it takes one step

>>> goal_seek(profit_by_sold, 0, 200)
(300.0, 1, 3, True)
>>> goal_seek(profit_by_price, 1200, 20)
(23.999999999999986, 1, 3, True)

Both searches finish after one secant step and three evaluations: the start, the second guess, and the answer. That is not luck. Profit is linear in items sold and linear in price, and a secant through two points of a straight line lands on its root exactly, apart from rounding.

The rounding is the interesting part. A currency-formatted cell would show $24, but the returned value is 23.999999999999986. The cause is the second guess. 20 × 1.01 is 20.2, and 20.2 has no exact binary representation, so the slope of the line carries a tiny error into the answer. Running the same single secant step with other second guesses shows it:

Second guess	Answer after one step
20.2	23.999999999999986
20.25	24.0
20.5	24.0
21	24.0

20.25, 20.5 and 21 are exact in binary, so the result is exactly 24. The same sheet in yen (price 2,000, second guess 2,020) gives exactly 2400.0. In Python, 23.999999999999986 == 24 is False, and an exact comparison in any later code or export can trip over it the same way. Wrapping the found value in ROUND before you build on it avoids that.

For comparison, plain bisection on items sold between 0 and 1,000 needed 24 evaluations to get within 0.001 of zero, and it ended at 299.9999523162842, not 300. On a linear sheet, the secant step is the whole method.

When profit is a curve, the start picks the answer

Real prices move sales. To see what the search does then, give the sheet a made-up demand line: 200 items at $20, two fewer for every extra dollar, so items sold = 240 − 2 × price. Profit becomes a hill:

curved = lambda p: (240 - 2 * p) * (p - 8) - 3600

Scanning prices in one-cent steps puts the top of the hill at $64, with a profit of $2,672 on 112 items. Every profit below that is reached twice, once on each side of the hill. Running the search from three starting prices:

Target profit	Start price	Answer	Items sold	Iterations	Evaluations
0	$20	$27.45	185	4	6
0	$60	$27.45	185	9	11
0	$90	$100.55	39	5	7
1,200	$20	$36.87	166	5	7
1,200	$60	$36.87	166	9	11
1,200	$90	$91.13	58	3	5
3,000	$20	none	–	100	102

Three things show up. Both break-even prices are real: $27.45 and $100.55 each give a profit of zero, and the search reports whichever one is downhill from where it started. A start near the top of the hill ($60) is the slowest, because the line through two points near a peak is almost flat and the first jumps overshoot, until a bracket forms and bisection takes over. And a target above the peak has no answer at all: after 100 iterations the search stops at a price that gives $2,481 and reports converged = False. The function never says "there are two answers" or "the best you can do is $2,672". It only says what it found from where it started.

Bisection on the break-even, given the bracket $8 to $64 by hand, needed 19 evaluations. The hybrid needed 6 from a start of $20, so the secant steps still pay off on a curve, as long as the start is on the right side.

What this means in a sheet

Put a sensible value in the changing cell before you run Goal Seek, because that value decides which answer comes back. If the result looks odd, run it again from a value on the other side. Read the found value with ROUND or a format, not as an exact number. And when the formula is linear, as most break-even sheets are, the closed form is one cell: =B3/(B1-B2) gives 300 directly. Goal Seek earns its place when the profit formula has tiers, fees or a demand curve, and the inverse is no longer a single division.

In the app, the dialog sits under Data, What-If Analysis, Goal Seek, and the result appears as "Goal Seeking found a solution: …". The Spreadsheet Editor ships in the free osFoundry desktop app, and the workbook stays on the local disk (local-first).