Goal seek how does it work

Technically, Goal Seek is a process of calculating a value by performing what-if analysis on a given set of values.

For our purposes, Excel's Goal Seek feature lets you adjust a value used in a formula to achieve a specific goal. Or, put another way, Goal Seek determines input values needed to achieve a specific goal. Use Goal Seek when you don't have an exact value to use.

Calculating the terms of a loan is a good example of a what-if situation. What if the payment your lender offers is too high for your budget? Using Goal Seek can help by evaluating the payment formula and returning new values for the rate, term, and even the principal to fit your desired payment.

Deposits matter, but we won't complicate this example with more variables than we need. You could spend a lot of time plugging in new rate and term values until you get lucky. A better solution is to use Goal Seek to determine the rate and term necessary to reduce your payment. First, let's calculate the necessary rate, as follows:. If Goal Seek doesn't work, revisit the To value in step 3. Specifically, you must enter the desired payment as a negative value.

To return to the original values, click Cancel in step 4 instead of clicking OK after viewing the result in the dialog. If Goal Seek can't find a solution for you, Excel enables the Step and Pause buttons, which allows you to force further iterations to reduce the gap between the current values and your goal. This is a more advanced feature that we won't visit in this article, but you should know that it's available. Now, you probably know that you can also lengthen the term of the loan to reduce the payment, so let's use Goal Seek to determine a new term as follows:.

This adjustment is as drastic as the rate adjustment. Clearly, the best route is to seek a lower interest rate if possible. If that's not possible, a home that's priced lower might be in your future. Let's try that too:. Goal Seek didn't give you the solution--only you can decide what to do with the information Goal Seek provides.

In the example, this reference is cell B3. Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box. Click OK. Goal Seek runs and produces a result, as shown in the following illustration.

On the Home tab, in the Number group, click Percentage. Click Increase Decimal or Decrease Decimal to set the number of decimal places. If you want to accept more than one input value, for example, both the loan amount and the monthly payment amount for a loan, use the Solver add-in.

Finally, format the target cell B3 so that it displays the result as a percentage. Follow one of these steps:. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Problem : You are running for some elected position where a two-thirds majority Assuming there are total voting members, how many votes do you have to secure?

Result : What-If analysis with Goal Seek shows that to achieve the two-thirds mark or Sometimes Goal Seek is not able to find a solution simply because it does not exist. In such situations, Excel will get the closest value and inform you that Goal Seeking may not have found a solution:.

If you are certain that a solution to the formula you are trying to resolve does exist, check out the following troubleshooting tips. First off, make sure the Set cell refers to the cell containing a formula, and then, check if the formula cell depends, directly or indirectly, on the changing cell. The below screenshot shows the default iteration settings:.

For Goal Seek or any Excel formula to work properly, the involved formulas should not be co-dependent on each other, i.

I thank you for reading and hope to see you on our blog next week! What is Excel Goal Seek? Goal Seek can process only one input value at a time. If you are working on an advanced business model with multiple input values, use the Solver add-in to find the optimal solution.