Goal Seek in Microsoft Excel

What is Goal seek? Goal Seek is part of Microsoft Excel’s What-If analysis tools. Goal seek is used to find an input value if you know the final result and therefore very useful. Let’s take, for example, a student who participates in an engineering entrance examination. He estimates that he may have scored certain marks in Physics, Chemistry and Math. Now he has to take his last exam in English. He knows from past experience that inspite of hard and smart work he has never been able to score more than 80. He now uses Goal Seek to estimate his chance of getting a seat in an engineering college based on his estimated performance, experience of scoring in English and the minimum marks required to get a seat.
He enters his estimated marks for Physics, Chemistry and Math in the worksheet cells B1, B2 and B3 with labels on the left in cells A1, A2 and A3. He enters nothing in cell B4 with a label English in cell A4.
In cell A5 he enters a label Average and calculates the averabe in cell B5 using the formula: =AVERAGE(B1:B4). He gets a certain result.

Goal Seek

Goal Seek

Now he knows that he needs to score a minimum average of 85 to get into an engineering college. He now uses Goal Seek to estimate his chances:

Click in cell B5 which has the calculated value based on the AVERAGE function.
Next he clicks on the data tab.
In the Data Tools tab he clicks on the drop down arrow next to the item ‘What-if Analysis’.
He selects ‘Goal Seek…’ because he wants to find the right input for the English marks.
In the new Goal Seek window that pops up.
Enter B5 next to Set cell.
Enter 85 (minimum marks required for entry to college) next to ‘To value:’
Click on cell B4 to enter its address next to ‘By changing cell:’
Click on OK.

Goal Seek performs an iteration:

Goal Seek Iteration

Goal Seek Iteration

The final result of Goal Seek in this example:

Goal Seek Final Analysis

Goal Seek Final Analysis

The situation is not so good for the student in the present scenario because he needs to score 105 and the maximum marks are 100!
Watch the Excel training video also to view another similar example. Later on we’ll learn how to use data tables to do a what-if analysis using Goal Seek.


Further reading:
Goal Seek in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *