How to Use Goal Seek in Excel

Goal Seek is one of Excel’s What-if Analysis tools that helps you to find the correct input value of a formula to get the desired output. It shows how one value in a formula affects another. In other words, if you have a target value you want to achieve, you can use goal seek to find the right input value for getting it.

For example, let’s say you scored a total of 75 marks in a subject and you need at least a 90 to get an S grade in that subject. Thankfully, you have one last test that might help you to raise your average score. You can use Goal Seek to figure out how much score do you need on that final test to get an S grade.

Goal Seek uses a trial-and-error method to back-track the problem by inputting in guesses until it arrives at the right result. Goal Seek can find the best input value for the formula in a matter of seconds that would’ve taken a long time to figure out manually. In this article, 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 consists of three parameters, viz.:

Set cell – This is the cell wherein the formula is entered. It specifies the cell in which you want the desired output.
To value – This is the Target / Desired Value that you want as a result of the goal seek operation.
By changing cell – This specifies the cell whose value needs to be adjusted to get the desired output.

How to Use Goal Seek in Excel: Example 1

To demonstrate 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%’, but you are unable to increase your investment, so you must increase your revenue to gain profit. But to how much? Goal seek will help you find it.

You use the following formula in cell B13 to calculate the profit percentage:

=(B12-B11)/B12

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 adjust. Every time 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 3 fields:

Set cell – Enter the cell reference that contains the formula (B13). This is the cell that is going to have your desired output.

To value – Enter the desired result you are trying to achieve (30%).

By changing cell – Insert the cell reference for the input value that you would want to change (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 the ‘$’ sign before the column letter and row number to make it an absolute cell.

When you’re finished, click ‘OK’ to test it.

Then a ‘Goal Seek Status’ dialog box will pop up and inform you if it found any solution as shown below. If a solution has been found, the input value in the ‘changing cell (B12)’ will be adjusted to a new value. This is what we want. 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 correct solution, it shows the closest value it can produce and tells you that the ‘Goal-Seeking may not have found a solution’ message.

What to Do When Excel Goal Seek is Not Working

However, if you’re certain that there is a solution, there are a few things you can try to fix this issue.

Check Goal Seek parameters and values

There are two things you should check: first, check if the ‘Set cell’ parameter refers to the formula cell. Second, make sure the formula cell (Set cell) depends, directly or indirectly, on the changing cell.

Adjusting the Iteration Settings

In Excel settings, you can change the number of possible attempts Excel can make to find the right solution as well as its accuracy.

To change iteration calculation settings, click ‘File’ from the tab list. Then, click ‘Options’ at the bottom.

On the Excel Options window, click ‘Formulas’ at the left-hand pane.

Under ‘Calculation options’ section, change these settings :

Maximum Iterations – It indicates the number of possible solutions excel will calculate; the higher the number the more iterations it can perform. 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 smaller number gives higher accuracy. For instance, if your input cell value equal to ‘0’ but Goal Seek stops calculating at ‘0.001’, changing this to ‘0.0001’ should solve the problem.

Increase the ‘Maximum Iterations’ value if you want Excel to test more possible solutions and decrease the ‘Maximum Change’ value if you want a more accurate result.

The screenshot below shows the default value:

No Circular References

When a formula refers back to its own cell, it’s called a circular reference. If you want Excel Goal Seek to work properly, formulas should not use circular reference.

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. Goal Seek can help you find the loan amount that produces a monthly payment (EMI) of ‘$1000’.

Input the three input variables that you are going to need to calculate your PMT calculation 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 an EMI amount of $1,327.97.

The syntax of PMT function:

=PMT(Interest Rate/12, Term, Principal)

The formula:

=PMT(B3/12,B4,-B2)

Select the cell B5 (formula cell) and go to Data –> What If Analysis –> Goal Seek.

Use these parameters 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)