564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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!|
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.
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:
To use goal seeking:
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).
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 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!
Some other pages relevant to the above blog include:
25 Aytoun Street