Excel Tip of the Week #40 - Goal Seek

Hello and welcome back to the Excel Tip of the Week.  This week we have a Creator post in which we look at one of my favourite Excel features – the Goal Seek function.

Goal Seek is used to find what input value you should put in one cell, in order to achieve a desired output in another cell.. I like to think of it as Excel doing the leg work for you by running a series of 'trial and error' scenarios until it reaches the right answer.  It's most frequently used when a calculation is too unwieldy to find and exact answer mathematically.

How to use it: Goal Seek lives in the “Data” menu tab, under “What-If Analysis”.

Once you have selected the Goal Seek option you will see this box: 

You should fill this box in using the following rules:
Set cell: This should be the output cell, the cell you want to achieve a specific result. In order for this process to work, there should be a formula in this cell (the goal seek will not change the formula itself – don’t worry).
To value: this is the output you want to see in the place identified in Set Cell.  Enter this as a number, not a cell reference.
By changing cell: This is the input cell you want to change to achieve the result you are looking for. This cell should be a normal number cell only, not a formula.  It also should feed into the set cell either directly or through other formulae.

So, if for example you wanted a project to achieve an IRR of 17% you would put the IRR output cell in the “set cell” box, put 0.17 in the “to value” box, and an input – such a selling price – in the “by changing cell” box. Excel would run a series of calculations for you to work out what selling price you would have to charge to realise this target IRR. 

Excel will continue to try to solve the equation until it is resolved or it realises it isn't possible (this is more likely to be due to incorrect references being entered than there being no answer) and will then inform you of the result.

What is really great about Goal Seek is that it only changes the input cell – it won’t change any formulae. So if you just want to see “what would happen if”, you can do this safe in the knowledge that if you aren't happy with the outcome you can quickly revert to what you had before.

It is also useful if you want to run through a number of scenarios quickly – what inputs you would need to obtain a set range of returns for example.  It’s a real time-saver if you need to find out an appropriate interest or discount rate – in one step you can run through many iterations that would be time consuming otherwise. 

The best way to learn what Goal Seek does is to watch it in action and try for yourself – with that in mind, take a look at the attached workbook for some worked examples.

Previous post - Comments 
TOTW index
Next post - Precision as displayed

Thanks to Emma Horton for her input on this Tip.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.