Excel’s Goal Seek is a What-If Analysis feature that allows backsolving calculations. It’s useful in situations where you have a known output value but want to find the input that dictates it.
In the ribbon, it is found in Data (tab) > What-If Analysis (Forecast group) > Goal Seek.
The Goal Seek window contains three fields:
- Set cell—the cell reference containing the output formula.
- To value—the target value of the output formula.
- By changing cell—the cell reference of the input value.
The example explained is found in the following workbook:
Jonathan is a UK university student who has completed five modules out of six for his degree. He’s currently averaging a grade of 68.4, putting him well on track to achieve an Upper Second-Class degree.
But this would be a bit anticlimactic for him…
With just one module to go, he knows if he puts in a Herculean effort and strains every sinew, he can obtain that First-Class Honours degree he so dearly desires.
What Jonathan doesn’t know though is the minimum module grade he needs to reach the all-important 70 mark.
The first things to understand are:
- K10:L15 contains the degree classification table with the minimum grade required for each
- H13:I19 houses the module results table with Jonathan’s grades listed
Set cell references K18, which uses the formula
=AVERAGE(I14:I19) to output Jonathan’s average grade across all modules.
The To value is 70 — the minimum grade for a First Class Honours degree. Unfortunately, this field must contain a static value as cell references are not permitted.
For the final field, By changing cell, I19 states the Module F grade, which requires the correct figure to achieve the target value (70) in K18.
Once the OK button is pressed, Goal Seek iterates using trial and error before reaching the final answer, which is then placed in I19.
The Average Grade updates to 70, triggering a change to the Degree Classification so First Class Honours is returned.
Finally, please check out this video of Goal Seek in action: