Working backwards from an answer with Goal Seek in Grid

Most of the time a spreadsheet goes forwards: you change the inputs and see the answer. Sometimes you know the answer you want and need the input that gets you there. What price do I need to charge to make a thousand pounds profit? How much can I borrow if the monthly payment must stay under a set amount? Goal Seek in Grid by Zaweba answers exactly that kind of question.

How it works

Goal Seek is on the Data tab. It asks for three things: the formula cell you're interested in, the target value you want it to show, and the input cell Grid is allowed to change. Grid then tries values in the input cell until the formula gives the result you asked for, and puts the answer in place.

The formula cell must depend on the input cell, directly or through other formulas. If it doesn't, no amount of changing the input will move the result, and Goal Seek can't help. Trace precedents on the formula cell is a quick way to check the link is there.

Examples worth trying

A break-even sheet: one cell for the selling price, one for units sold, one for fixed costs and one for the cost per unit, with a profit formula. Set the profit cell to zero by changing the units cell, and Goal Seek tells you how many you need to sell to cover costs. Set it to your target profit instead, or change the price cell, to explore other angles.

A loan: a cell for the amount borrowed, the rate and the term, with a PMT formula for the monthly payment. Ask Goal Seek to set the payment to what you can afford by changing the amount, and it finds the largest loan that fits. Because PMT gives a negative figure for money you pay out, set the target as a negative too, or flip the sign in the formula first.

A savings plan: an FV formula for what the pot will hold. Set it to your goal by changing the monthly payment and you see what you need to put away.

A marks sheet: an average across several assessments with one still to come. Set the average to the grade boundary by changing the last mark and you see what a pupil needs to score.

Getting good results

Lay the sheet out with each input in its own clearly labelled cell, not typed into formulas. Goal Seek can only change a cell, so a number buried inside a formula is out of its reach.

Goal Seek changes the input cell to the answer it finds. If you want to keep the original value, note it first or press Undo afterwards. Some people keep a copy of the input beside it, labelled "original", so the comparison is always visible.

Be realistic about the question. Goal Seek changes one input to hit one target. If the answer it finds is impossible in real life, like a negative number of units, the model is telling you the target can't be reached with the other inputs as they are. Change one of those and try again.

For a result that rises and falls rather than moving steadily, start the input near the answer you expect, so the search settles on the sensible solution.

Where Goal Seek fits

Goal Seek works on any formula, so it pairs well with the financial, statistical and maths functions. Use it to explore a question, then build the answer into the sheet with a formula if it's something you'll ask often. It's one of the quickest ways to turn a static spreadsheet in Grid by Zaweba into a tool for making decisions.