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

## 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**.

Follow the steps below to do this.

**Steps:**

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

- In the
**Goal Seek**dialog box, set:

**Se t cell :** C9

**To**-1500 (You may want to know why we are giving the “-” sign here. The reason is that it’s a loan amount and the

__v__alue :**PMT**function considers the loan as a negative value.)

**By**$C$6

__c__hanging cell :- Press
**OK**, and you will get the desired output where the loan amount becomes $148,155.

### 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)`

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**=>as shown in the above example.__G__oal Seek… - In the
**Goal Seek**dialog box, give input like the following:

**Se t cell:** C9

**To**54,636.35

__v__alue:**By**$C$8

__c__hanging cell:- Press
**OK**.

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

### 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)`

To do so, follow the steps below.

**Steps:**

- In the
**Goal Seek**dialog box, give the following input:

**Se t cell :** C12

**To**75

__v__alue :**By**$C$10

__c__hanging cell :- 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:

## 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**.

Follow the steps below.

**Steps:**

- 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**Assig**__n__Macro…

- 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 =>**Assig**=> Paste the following code.__n__Macro…

```
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:

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

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

- Select
**Formulas**=> Change the**Maximum**value to 0.0000001.__C__hange

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.

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

## 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

- 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

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