Goal Seek is one of Excel’s built-in What-If Analysis tool that helps you find the input value in a formula to get a desired result.
Goal Seek is one of Excel’s What-if Analysis tools that helps you to find the input value of a formula to get the desired output. It shows how one value in a formula impacts another. In simple words, if you have a target value you want to achieve, you can use goal seek to find the best input value for getting it.
For example, let’s say you scored a total of 65 marks in a subject and you need at least an 80 to get an A grade in that subject. Luckily, you have one final assignment that might help you to raise your average. You can use Goal Seek to figure out how much score do you need on that final assignment to get an A grade.
Goal Seek essentially uses a trial-and-error method to back-track the problem by inputting in guesses until it arrives at the answer. It can find out the exact answer in a matter of seconds that would’ve taken a long time to find out manually. In this tutorial, we’ll show you how to use the Goal Seek tool in Excel with some examples.
Components of Goal Seek Function
The Goal Seek Function comprises of three parameters, namely:
- Set cell – This is the cell wherein the formula is entered. It specifies the cell in which you want the desired result.
- To value – This is the Target / Desired Value that you want as a result of the Goal Seek tool.
- By changing cell – This specifies the cell whose value needs to be changed to achieve the desired result after the Goal Seek operation.
How to Use Goal Seek in Excel: Example 1
To illustrate how it works in a simple example, imagine you run a fruit stall. In the below screenshot, cell B11 (below) displays how much it cost you to buy fruits for your stall and cell B12 shows your total revenue on selling those fruits. And cell B13 shows the percentage of your profit (20%).
If you would like your increase your profit to ‘30%’ of your income, and assuming that you are unable to increase your investment, you realize that you must increase your revenue. But to how much? Goal seek will help you find it.
You use the following formula in cell B13 to calculate the profit percentage:
Now, you want to calculate the profit margin so that your profit percentage is 30%. You can do this with Goal Seek in Excel.
The first thing to do is select the cell whose value you want to change. Whenever you use Goal Seek, you must select a cell that contains a formula or function. In our case, we’ll select cell B13 because it contains the formula to calculate the percentage.
Then go to the ‘Data’ tab, click the ‘What if Analysis’ button in the Forecast group, and select ‘Goal Seek’.
The Goal Seek dialog box will appear with three fields:
- Set cell – Enter the cell reference that contains the formula (B13). This is the cell that will contain your desired result.
- To value – Enter the desired result you are trying to achieve (30%).
- By changing cell – Enter the reference for the input cell that you would want to adjust (B12) in order to arrive at the desired result. Simply click on the cell or manually enter the cell reference. When you select the cell, Excel will add ‘$’ sign before the column letter and row number to make it an absolute cell.
When you’re done, click ‘OK’ to test it.
The Goal Seek Status dialog box will appear and inform you if a solution has been found. If a solution has been found, the input value in the ‘changing cell’ will be adjusted to a new one. So in this example, the analysis determined that, in order for you to reach your 30% profit goal, you need to achieve a revenue of $1635.5 (B12).
Click ‘OK’ and Excel will change the cell values or click ‘Cancel’ to discard the solution and restore the original value.
The input value (1635.5) in cell B12 is what we found out using Goal Seek in order to achieve our goal (30%).
Things to Remember when using Goal Seek
- The Set Cell must always contain a formula that is dependent on the cell ‘By Changing Cell’.
- You can only use Goal Seek on a single cell input value at a time
- The ‘By changing cell’ must contain a value and not a formula.
- If Goal Seek cannot find the exact solution, it displays the closest value it has come up with and informs you that the ‘Goal-Seeking may not have found a solution’.
What to Do When Excel Goal Seek is Not Working
However, if you’re sure that a solution does exist, there are a few things you can try to fix this issue.
Check Goal Seek parameters and values
First, make sure the Set cell refers to the cell containing a formula, and check if the formula cell (Set cell) depends, directly or indirectly, on the changing cell.
Adjusting the Iteration Settings
In Excel setting, you can adjust the number of possible solutions Excel will calculate as well as its accuracy.
To change them, click ‘File’ from the tab list. Then, click ‘Options’ at the bottom.
On the Excel Options window, click ‘Formulas’ at the left-hand sidebar.
Here, change these settings under ‘Calculation options’:
- Maximum Iterations – It indicates the number of possible solutions; the higher the number the more iterations. Increase this number if you want Excel to test more possible solutions. For example, if you set ‘Maximum Iterations’ to 150, Excel test out 150 possible solutions.
- Maximum Change – It indicates the accuracy of the results; the lower the number the higher the accuracy. Decrease this number if your formula requires more accuracy. For instance, if your input cell equal to 0 but Goal Seek stops at 0.001, setting Maximum Change to 0.0001 should fix the issue.
The screenshot below shows the default value:
No Circular References
When a formula refers back to its own cell directly or indirectly, it’s called a circular reference. For Excel Goal Seek to work properly, the involved formulas should not be co-dependent on each other.
Excel Goal Seek Example 2
Let’s say you’re borrowing money of ‘$25000’ from someone. They loan you the money at an interest rate of 7% per month for a period of 20 months, which makes the repayment of ‘$1327’ per month. But you can only afford to pay ‘$1,000’ per month for 20 months with a 7% interest. Use Goal Seek in Excel to find the loan amount that produces a monthly payment (EMI) of ‘$1000’.
Enter the 3 input variables that you will need to use for your PMT formula i.e. interest rate of 7%, term of 20 months, and principal amount of $25,000.
Enter the following PMT formula in cell B5 which will give you a monthly payment amount of $1,327.97.
The syntax of PMT function:
=PMT(Interest Rate/12, Term, Principal)
Select the cell B5 (formula cell) and go to Data –> What If Analysis –> Goal Seek.
Here’s what you should enter on the ‘Goal Seek’ window:
- Set cell – B5 (the cell which contains the formula that calculates EMI)
- To value – 1000 (the formula result/goal you are looking for)
- By changing cell – B2 (this is the loan amount that you want to change to achieve the goal value)
Then, hit ‘OK’ to keep the found solution or ‘Cancel’ to discard it.
The analysis tells you that the maximum loan amount you can borrow is $18825 if you want to stay within the budget.
That’s how you use Goal Seek in Excel.