Goal Seek

Goal Seek changes one input cell until a formula cell reaches the value you want. It's Excel's What-If Analysis → Goal Seek.

One cell

1. Open the sidebar and stay on the Goal Seek tab.
2. Set cell: the formula you want to hit a value, e.g. a monthly payment in B10.
3. To value: the number you want, e.g. 2000.
4. By changing cell: an input the formula depends on, e.g. the loan amount in B3.
5. Press Seek. If you don't like the answer, Undo puts the old value back.

It tries a couple of values, then narrows in. For a formula that is a straight line in the input it's done in two or three recalculations. Curvy formulas take a few more.

If there's no answer (say, a payment that can't go below zero), it says so and leaves your sheet as it was, apart from the closest value it found, which Undo removes.

Many cells at once Pro

The Multi tab does the same for a whole range. Example: prices in B2:B100, margins in D2:D100, and you want every margin at 25%.

• Set cells: D2:D100
• To value: 0.25, or a range of targets the same size, like E2:E100
• By changing cells: B2:B100

Cells are tells it how the rows relate:

• Independent: each row only depends on its own input. All rows are solved together in the same recalculations, so 200 rows take about as long as one.
• Linked: the formulas depend on each other's inputs (for example a set of equations). They're solved as one system.
• Work it out for me: checks which one it is first.