Goal Seek in Excel means using the Goal Seek tool of Excel to calculate the specific input value that will produce a desired result in a formula.
In this Excel tutorial, we will learn how to do the Goal Seek analysis in Excel.
In the following overview image, we used $180,000 as the loan amount first and got a monthly payable value of $1822. Using the Goal Seek analysis, we have brought down this monthly payable amount to $1500, changing the loan amount. The final loan amount becomes $148,155.
In this blog post, we will use Goal Seek to calculate the highest loan amount for a fixed monthly installment, the time required for an expected profit return from a fixed deposit, and the minimum number to get in an exam for a specific grade. We will also learn how to automate the Goal Seek analysis. Moreover, we will see how to get a more precise value in Goal Seek analysis.
⏷What is Goal Seek in Excel?
⏷How to Use Goal Seek in Excel: 3 Practical Examples
⏵Getting the Highest Loan Amount for a Fixed Monthly Installment
⏵ Calculating The Time Required For Desired Profit
⏵ Minimum Number to Get in Final Exam for a Fixed Grade
⏷How to Automate Goal Seek in Excel
⏷How to Get More Precise Value Using Goal Seek
⏷How to Fix If Goal Seek Is Not Working in Excel
⏷Advantages of Goal Seek Analysis in Excel
⏷Disadvantages of Goal Seek Analysis in Excel
Goal Seek is a built-in tool in Excel to perform What-If Analysis. It calculates the value we need to give as input to a formula to get the desired output.
While performing Goal Seek analysis, it will ask for three parameters. Detailed descriptions of those parameters are given below.
|A cell that contains the formula also contains a value we want to change.
|Desired target value.
|By changing cell:
|Goal Seek will adjust the value in this cell to get the desired value using the formula.
To illustrate where to use Goal Seek analysis, let’s imagine we know various types of costs and total costs of our business but do not know the amount of product we need to sell to reach the break-even point. We will set the profit equal to our total cost, and we will calculate the number of sales. This is a type of reverse calculation where we use Goal Seek to find an input value in Excel.
In this section, we will use the Goal Seek feature in Excel with three examples to perform a what-if analysis. The first example will calculate the possible loan amount for a fixed monthly installment. The second example shows how to get the number of years required to get the desired profit. The final one calculates the number required to get a fixed grade.
In this section, we will find out the highest loan amount we can get for a fixed monthly installment. In the following dataset, we see that a person has taken a $180,000 loan from a bank. The annual interest rate is 4%, and he wants to pay the loan over a 10-year period.
Using the PMT function in the following formula, we get the monthly payable amount of $1,822.41.
Suppose the person wishes to pay $1,500, not $1,822.41. We will do it using Goal Seek in cell C9.
Follow the steps below to do this.
- Click in cell C9 => Select Forecast drop-down in the Data tab => What-If Analysis => Goal Seek…
- In the Goal Seek dialog box, set:
Set cell : C9
To value : -1500 (You may want to know why we are giving the “-” sign here. The reason is that it’s a loan amount and the PMT function considers the loan as a negative value.)
By changing cell : $C$6
- Press OK, and you will get the desired output where the loan amount becomes $148,155.
In this section, we will use Goal Seek to calculate the time it will take to return the desired profit from a fixed deposit amount.
In the following dataset, we have a fixed deposit amount in cell C6. The return amount for this deposit in 2 years is in cell C9. But we want to get a profit amount equal to the amount in C10.
Using the FV function, you can calculate the return on investment after 2 years.
Follow the steps below to find out the time required using the Goal Seek analysis.
- Click in cell C9 => Go to Forecast drop-down under the Data tab => What-If Analysis => Goal Seek… as shown in the above example.
- In the Goal Seek dialog box, give input like the following:
Set cell: C9
To value: 54,636.35
By changing cell: $C$8
- Press OK.
We will get the following output, where the required time to get the desired profit is 3 years.
In the following section, we will determine the number a student needs to get so that he gets a fixed grade.
In the following dataset, we have the names of exams in range B7:B10 and the number of students in range C7:E9. C12:E12 cells contain the final marks of the respective students by doing an average of the 4 exams. We want the final marks to be 75 for all. To get this output, we will find out the numbers to be obtained in Exam 4 by performing the Goal Seek analysis in cell C10.
We use the AVERAGE function in the following formula to calculate the average of four exams for a student.
To do so, follow the steps below.
- In the Goal Seek dialog box, give the following input:
Set cell : C12
To value : 75
By changing cell : $C$10
- Press OK.
We will get the required marks in Exam 4 in cell C10.
Follow the same steps for other students, setting the same final marks or different marks. We have done this by setting different marks for different students, and the output is like the following image:
We have a dataset that contains the names of some projects in the range B7:B15 and the hours required to finish those projects in the range C7:C15. We will input the project’s progress in cell G7. The code will use Goal Seek to assign the same value in the range D7:D15. The VBA code will return the hours required to finish that percent of work in the range E7:E15.
Follow the steps below.
- Click on the Developer tab => Insert => Button (Form Control).
- Drag the plus icon that has appeared in your worksheet and adjust it to a suitable size.
- Click on the button => CTRL+C to copy => CTRL+V to paste a new button.
- Assign the name “Goal Seek” to the first button and “Reset Progress” to the second one.
- Right-click on the Goal Seek button => Select Assign Macro…
- Copy and paste the following code in the code window.
Dim m As Integer
Dim k As Integer
'Assign formula to cells D7 to D15
For m = 7 To 15
Cells(m, "D").GoalSeek Goal:=Cells(7, "G"),
The above code will assign values to the cells in the range D7:E15. We will make it dynamic by assigning code to the Reset Progress button.
- Right-click on the Reset Progress button => Assign Macro… => Paste the following code.
'Selecting cells and clearing contents
We have automated the Goal Seek analysis.
- In cell G7, assign the value “60%” and click on the button Goal Seek. You will get the following output:
- Click on the button Reset Progress to remove values in the range D7:E15, and also in G7.
In the following gif, you will get an idea of how the automation of Goal Seek works.
Goal Seek gives results based on the default precision setup of Excel. If you want a more precise value, you can change it in the settings.
To do that, you will have to follow the steps below.
- Click File => Options from the left pane. The Excel Options dialog box will open.
- Select Formulas => Change the Maximum Change value to 0.0000001.
Now, you will get a more precise value in the Goal Seek analysis.
In some scenarios, you may find that Goal Seek is not working. There are two main reasons behind this.
1. Wrong Cell Reference or No Formula in Set Cell
Excel will give you a warning if you have given the wrong cell reference in the parameters or if the Set Cell: does not contain a formula. In the following image, you will find that Goal Seek is not working since the cell does not contain a formula.
2. Circular Reference in Cell
Another case when Goal Seek does not work is when there is a circular reference in any cell that you have given input in the Goal Seek parameters. So, ensure no circular reference in the parameters of the Goal Seek analysis.
The main advantage of using the Goal Seek analysis is that you can get the input value in a formula while the output is known. For example, you can use this to get the price of a product when you know the cost of the product and the intended profit amount. Also, if you have a fixed price for your product, you can use this feature to determine the number of products to be sold for a targeted profit.
– In some cases, the Goal Seek analysis may be problematic. For example, if you do this analysis on vote casting in an election, the total number of votes may increase or decrease from the real value.
– When the input value is far above or below the desired output value, Goal Seek analysis may not give accurate results.
– Instead of hard coding, it is better to use a cell reference to modify easily.
Download Practice Workbook
Download the workbook below to practice yourself.
This article has shown three examples of using Goal Seek analysis in Excel. To apply Goal Seek to more than one cell, we have used a VBA code. Also, we have shown how to increase precision in Goal Seek analysis. Finally, we have explained some advantages and disadvantages of using the Goal Seek analysis. Leave a comment for any further queries.
Goal Seek in Excel: Knowledge Hub
- How to Use Goal Seek in Excel
- How to Automate Goal Seek in Excel
- How to Do What-If Analysis Using Goal Seek in Excel
- How to Use Goal Seek to Find an Input Value
- How to Do Reverse What-If Analysis in Excel
- Excel Macro to Goal Seek for Multiple Cells