BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andrew Gould on 24 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Goal Seeking in Microsoft Excel
Normally in Excel you create a calculation to find out what result you get from a specific set of input values. You can then change the input values to see a new result calculated. Goal seeking allows you to approach that process from the opposite direction: when you have a calculation set up you can manipulate the calculation to obtain a specific result and get Excel to tell you what inputs you need in order to hit that target.
You can watch a video version of this goal-seeking tutorial.
Goal Seeking with Simple Calculations
To demonstrate the principle of goal seeking we'll start with a straightforward calculation:
This system has two inputs (the green cells) and one calculation (the orange cell).
In this simple example we can use goal seeking to set the result of our calculation to a specific value, say £5000. We can then tell Excel to tell us either:
- What quantity we would have to sell at the current price in order to meet that target; or
- At what price we would have to sell the current quantity to meet the target.
To use goal seeking:
- Select the cell containing the calculation you want to alter the result of. In our example this is cell B6.
- From the ribbon select: Data -> What If Analysis -> Goal Seek
- Complete the dialog box as shown below:
Here we're setting the result of the formula in B6 to a value of 5000 by changing the value in cell B3 (the quantity in our system).
- Click OK to ask Excel to try to find a solution for the values you have entered.
If a solution is found you can click OK to keep the values Excel has set, or click Cancel to revert to the original cell values.
You can run another goal seek on the same cells as many times as you like, trying different combinations of values each time.
Goal Seeking with a Sequence of Calculations
Goal seeking doesn't have to be applied to just simple calculations like the one above. In that system the input feeds directly into the calculation whose result we were trying to control, but in the example shown below things are a little more complicated:
As in the above example, green cells represent inputs and orange cells contain calculations.
Even though the value of cell B2 doesn't feed directly into the calculation in cell B12 in the above example, we can still use goal seeking to influence their values. For this example we'll imagine that we couldn't afford a monthly payment of more than £2,400 and we want Excel to tell us what the maximum house price would be if all of the other inputs stay the same.
Using these settings Excel should tell us the maximum house price we can afford while keeping our monthly payments manageable.
We lose a significant chunk from the value of house we can afford, but at least we'll be able to keep up with the payments!