Microsoft Excel - Goal Seek

Goal Seek 

Goal seeking is a method of finding a specific value for one cell by adjusting the value of another cell. Excel varies the value in a changing cell until a formula that is dependant on that cell returns a certain target result, this is used when you know the desired result of a formula but not the input value that the formula needs to determine the result.

In short, back calculation.

In  the illustration, we are doing the costing for batches of Gin & Tonic. We know that we can sell them for £8 but we cant to make a profit of £1 on each batch.


What should the measure be in order to achieve this profit?

And the answer is um, err… Worksheet calculations can be so complicated that it not possible to even guess.



Make sure that the cells that you are using are dependant on each other and use Formula Auditing with the tracer arrows if you are not sure.

Then choose the Data Tab → What if Analysis → Goal Seek.

The Set cell is the cell containing the formula where you want to force a certain result, To Value is the target result and by changing cell is one that is precedent to the Set cell.

Goal Seek will calculate the mathematical possibility of an outcome but can produce unrealistic results as you can not place constraints on the changing cell or involve other cells in the calculation.

For working out more complex problems use Excel Solver.