How to find the input that gives a target answer
Goal Seek in Grid by Zaweba works backwards: set the answer you want in a formula cell, name the input to change, and Grid finds the value for you.
Goal Seek does exactly this. In Grid by Zaweba, Data > Goal Seek asks for a formula cell, the value you want it to show, and the input cell it may change. Grid then finds the input that makes the formula produce your target, and puts it in the input cell.
Where it's useful
Most of the time you change inputs and watch the answer. Goal Seek answers the reverse question. What price do I need to charge to break even? How much can I borrow if I can pay 600 a month? What mark does a student need in the final exam to reach a 70 average? What sales figure hits this month's target after costs? Each one has a formula that gives an answer and one input you're free to change.
Before you run it
A working model. The answer cell must contain a formula, and that formula must depend, directly or through other cells, on the input cell. The input cell must hold a plain value, not a formula, since Goal Seek is going to type a number into it.
If you're not sure the two are connected, trace precedents from the answer cell. The arrows show which cells it reads, and if they lead back to your input, Goal Seek can work with it.
Using Goal Seek step by step
Choose Data > Goal Seek. In the first box, give the cell with the formula. In the second, type the value you'd like it to show. In the third, give the cell Grid is allowed to change. Run it, and Grid tries values for the input until the formula reaches the target. The input cell now holds the answer, and every formula that depends on it has recalculated.
Look at the result before you accept it. If it isn't what you need, undo puts the original input back, since undo in Grid covers every change.
Examples worth trying
Break-even: a profit cell that subtracts costs from price times quantity. Seek a profit of zero by changing the price, and you have the break-even price.
Borrowing: a payment cell using PMT on a loan amount. Seek your affordable payment by changing the loan amount, and you know how much you could borrow at that rate and term. Remember PMT returns money out as a negative, so seek a negative payment or flip the sign in the formula.
Exam marks: a weighted average across coursework and an exam. Seek 70 by changing the exam mark to see what score is needed.
When Goal Seek can't find an answer
Some targets are impossible. If the formula can never reach the value, for example a profit target higher than the market could ever pay, Goal Seek won't find a solution. Check whether the target makes sense.
Others have more than one answer. A formula with a curve in it may reach the target at two different inputs, and Goal Seek will find the one nearest where it started. Changing the starting value of the input steers it towards the other.
Goal Seek changes one input. For questions with several inputs at once, try a small table of values instead, with the answer worked out for each row, and read off the row closest to your target. Grid does not have Solver, Scenario Manager or What-If data tables.
Keeping the model honest
Label the input and answer cells clearly, and keep inputs together at the top of the sheet. It makes Goal Seek quicker to set up and makes it obvious which cell has been changed.
Goal Seek turns a trial-and-error afternoon into a few seconds. In Grid by Zaweba it sits on the Data tab, ready for the next time you know the answer and need the question.
