Excel includes several tools for data analysis and Goal Seek is a technique used to easily find the number that fulfills the necessary conditions to achieve a goal.
This tool will help you do many tests of values in a formula to find the exact value that meets the conditions. That is, if we know the result of a formula, but we are not sure of the value of one of its arguments, then Goal Seek will help us to test various scenarios to find the exact value needed. For example, suppose we have the following problem.
I have a number multiplied by another number X and this should give me the result shown. Although this is an easy problem to solve mathematically will be useful to show the functionality of Goal Seek. I begin by defining an important formula. In cell C3 will place the formula to be met to obtain the desired result, ie, = B1 * B2 it should result in 689
For now the result in C3 is zero, because there is no value in cell B2, however, when you go looking for the appropriate value for B2, the value of cell C3 will become the same as B3 when the correct value is found.
Goal Seek Command
Now you need to click the Data tab and select If Analysis where an options menu will be displayed and you must choose Goal Seek.
Immediately the dialog box Goal Seek is displayed, where you must fill in the following text boxes.
– Set cell: It is the cell that contains the formula – To Value: This is the number we want as a result of the formula. – By changing cell. The cell to be modified to have the desired result. Once the parameters have been set, you must click on the OK button and Excel will begin to enhance the necessary calculations. When have found a suitable result the Goal Seek Status dialog box is displayed.
This dialog box just notified that it has reached the target by changing the specified cell. When you close the dialog box you can see the result. For our example Excel was doing several tests until the result of 146,60 is the number multiplied by 4,7 will give the expected result.
(Source: Excel Total)