This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Goal Seeking in Microsoft Excel
Goal seeking in Excel allows you to approach calculations from the "result end" and get Excel to tell you what inputs you need in order to hit a specific target. Read on to find out how it works!

Posted by Andrew Gould on 24 February 2012

In this blog

Goal Seeking with Simple Calculations
Goal Seeking with a Sequence of Calculations

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.