StatPakGoal Seek in Google Sheets
Goal Seek changes one input cell until a formula cell reaches the value you want. Google Sheets has no Goal Seek menu of its own, so you need an add-on.
Example: what monthly payment pays off a loan?
A $20,000 loan at 6% a year (0.5% a month) over 60 months. Put a guess for the payment in B2 and the balance left after 60 months in B5:
=FV(0.005, 60, -B2, 20000)
- Open Extensions, StatPak, Goal Seek.
- Set cell:
B5. To value:0. By changing cell:B2. - Click Find. B2 becomes 386.656, the payment that leaves a zero balance. You can check it with
=PMT(0.005, 60, -20000).
Other uses
- Break-even: set Profit to 0 by changing Units sold.
- Required growth: set Year 5 revenue to a target by changing the growth rate.
- Grade needed: set the course average to 70 by changing the final exam score.
Tips
- The Set cell must contain a formula that depends, directly or through other cells, on the changing cell.
- Start the changing cell at a sensible guess. Goal Seek searches outward from the current value.
- If there is no solution (for example a target the formula can never reach), Goal Seek reports the closest value and restores your original input.
- For several inputs or constraints, use Solver instead.