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!