How to use Goal Seek in Excel
Goal Seek is a feature in Microsoft Excel that allows you to find the input value that will produce the desired output value in a formula.
In this tutorial, you will learn how to use Goal Seek in Excel.
Data preparation
Goal Seek requires you to have a formula in a cell and a desired output value. For example, you might have a formula that calculates the total cost of a purchase, and you want to know how many items you can buy with a budget.
In this example, price is $230 and your budget is $1,000,000. How many pieces can you afford? Select the cell containing the formula that calculates your desired outcome; this is the Set cell in Goal Seek. In our example, this would be the cell containing the total cost calculation, which should ultimately equal your budget.
What-if Analysis
To use Goal Seek, go to the Data tab and click the What-If Analysis button. Then, select Goal Seek from the list.
The Goal Seek dialog box will open. In the Set cell field, select the cell that contains the formula. In the To value field, enter the desired output value. In the By changing cell field, select the cell that contains the input value that you want to change.
Click OK to start the Goal Seek process. Excel will adjust the input value in the By changing cell field until the output value in the Set cell field matches the desired value.
The Goal Seek process will stop when the output value in the Set cell field matches the desired value. The input value in the By changing cell field will be the value that you need to change to achieve the desired output value.
As you see, Goal Seek is a very easy way to calculate results. Try to learn for your own with more difficult examples.
Keep in mind that the input value that Goal Seek finds is only an estimate, so you may need to perform the process multiple times to find an accurate result.
Goal Seek is most effective when there is a single input value that directly affects the output formula. It may not provide accurate results for complex scenarios with multiple interdependent variables or when the relationship between the input and output is not linear. In such cases, other What-If Analysis tools like Solver might be more appropriate.
Leave a Reply