Have you ever tried to work out what you need to change a figure to in order to hit a target value? Do you end up randomly changing a figure on your spreadsheet until you get close to what you need the figure to be? Does this take you ages? Do you think there must be a better way?
Well there is!! The solution is goal seek!
Working with Goal Seek
Goal seek enables a cell containing a calculation to be set to a specific value by changing a cell that affects that value either directly in one calculation or indirectly through a chain of calculations.
- On the Data tab choose What-If Analysis and Goal Seek
- Change the Set Cell to the cell reference that needs to be set to specific value
- Change the To Value to the value you want the Set Cell to be changed to
- Change the By Changing Cell to the cell that will be altered to reach the value required
In the spreadsheet below we are looking to find out how many computers need to be sold to reach a turnover of £100,000.
The goal seek dialog box would need to be set as follows.
We would be setting the total turnover in B8 to £100,000 by changing the number of computers sold in B5
Goal Seek Status
The goal seek status will inform you whether a solution has been achieved or not. The way Excel tries to find a solution is using a process called iteration where it tries numbers which get closer and closer to the result but a solution is not always possible.
OK will accept the solution or Cancel will return the figures back to what they were before the goal seek was started.
This is a really simple example but the more complex the combination of calculations are that affect the result the more difficult this is to achieve manually and the more useful Goal Seek becomes.
This can be particularly useful for sales forecasting and projections but is actually really helpful for anything where a target figure is looking to be met.
If you’ve enjoyed reading about the Goal Seek feature in Excel, there’s some other blogs below that you might find useful: