Goal Seek in Excel (Examples, Advantages, and Disadvantages)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of Goal Seek in Excel

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


What Is Goal Seek 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.

Parameter Compulsory/Optional Explanation
Set Cell: Compulsory A cell that contains the formula also contains a value we want to change.
To value: Compulsory Desired target value.
By changing cell: Compulsory 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.


How to Use Goal Seek in Excel: 3 Practical Examples

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.


1. Getting the Highest Loan Amount for a Fixed Monthly Installment

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.

=PMT(C7/12,C8*12,C6)

Suppose the person wishes to pay $1,500, not $1,822.41. We will do it using Goal Seek in cell C9.

Current Monthly Payment and Targeted Payment

Follow the steps below to do this.

Steps:

  • Click in cell C9 => Select Forecast drop-down in the Data tab => What-If Analysis => Goal Seek…

Navigating Goal Seek in the Data Tab

  • 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

Giving Input to Goal Seek Dialog Box

  • Press OK, and you will get the desired output where the loan amount becomes $148,155.

Output of Goal Seek Analysis


2. Calculating the Time Required for Desired Profit Return

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.

=FV(C7,C8,0,-C6,0)

Dataset For Time Analysis of Desired Profit

Follow the steps below to find out the time required using the Goal Seek analysis.

Steps:

  • 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.

Input Values For Time Analysis of Desired Profit

We will get the following output, where the required time to get the desired profit is 3 years.

Output of Goal Seek Analysis For Required Time


3. Minimum Number to Get in Final Exam for a Fixed Grade

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.

=AVERAGE(C7:C10)

Dataset For Calculating Number For Fixed Grade

To do so, follow the steps below.

Steps:

  • In the Goal Seek dialog box, give the following input:

Set cell : C12
To value : 75
By changing cell : $C$10

  • Press OK.

Giving Input to Goal Seek Dialog Box

We will get the required marks in Exam 4 in cell C10.

Output of Goal Seek in Calculating Fixed Grade

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:

Goal Analysis For Multiple Students


How to Automate Goal Seek in Excel

In this section, we will learn how to automate Goal Seek in Excel. More specifically, we will use Excel Macro to Goal Seek for multiple cells.

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.

Dataset For Automating Goal Seek Analysis

Follow the steps below.

Steps:

  • Click on the Developer tab => Insert => Button (Form Control).

Insert Button in Worksheet

  • 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.

Copy-Paste 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…

Assigning Macro to Goal Seek Button

  • Copy and paste the following code in the code window.
Sub Button5_Click()
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"), 
    changingCell:=Cells(m, "E")
Next m
End Sub 
			

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.
Sub Button4_Click()
 'Selecting cells and clearing contents
 Range("E7:E15,G7").Select
 Selection.ClearContents
End Sub 
			

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:

Getting Output Using Automated Goal Seek Method

  • Click on the button Reset Progress to remove values in the range D7:E15, and also in G7.

Removing Contents From Cells

In the following gif, you will get an idea of how the automation of Goal Seek works.

Goal Seek Analysis Automated


How to Get More Precise Value Using Goal Seek Feature

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.

Steps:

  • Click File => Options from the left pane. The Excel Options dialog box will open.

Excel Options Dialog Box

  • Select Formulas => Change the Maximum Change value to 0.0000001.

Changing Value For More Precision

Now, you will get a more precise value in the Goal Seek analysis.


How to Fix If Goal Seek Is Not Working in Excel

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.

Goal Seek Not Working Because of No 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.

Goal Seek Not Working For Circular Reference


Advantages of Goal Seek Analysis in Excel

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.


Disadvantages of Goal Seek Analysis in Excel

– 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


<< Go Back to What-If Analysis in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Junaed-Ar-Rahman
Junaed-Ar-Rahman

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo