What-if analysis is a process through which you can see the outcome by changing any cell in the dataset. In Microsoft Excel, you can get three different types of what-if analysis. The Goal Seek Analysis is one of them. This article will show the goal seek analysis in Excel. I hope you find this article informative and gain lots of knowledge.

**Table of Contents**hide

## Download Practice Workbook

Download the practice workbook below.

## Overview of Excel What-If Analysis: Goal Seek

What-if analysis is a process through which you can see the outcome by changing any cell in the dataset. By using the **what-if analysis in Excel**, you can use several sets of values and get the desired outcome. For example, in the what-if analysis, you can calculate the total expenses of several cars and finally select your preferred car. That means, using the what-if analysis, you can establish a proper overview of all types of things and also denotes how it reacts in the future.

The main purpose of the what-if analysis in Excel is to determine the outcome in a statistical mood and do the risk assessment. The main advantage to use what-if analysis in Excel is that there is no need to create a new worksheet but we can get the final results for different inputs. There are three types of what-if analysis in Excel: **Scenario**,** Goal Seek** and **Data Table**.

The **Goal Seek **helps to measure the required amount of something beforehand if you have the desired result in your mind. That means if you have the result in your mind but don’t know how to change the input value to get the desired result. In that case, goal seek can help you. For example, you know what you want to get in the final result, but don’t know how much you need to score to touch that grade. Goal seek will help you to do that effectively.

## Advantages of Using Goal Seek Analysis in Excel

The main advantage of using goal seek analysis is to measure a required amount of something beforehand if you have the desired result in mind. If you have a loan and say you want to keep the interest within a limit. Then you can easily find out the amount of interest or the amount of time you need to pay off the loan, or even the monthly installments of payments to limit the total interest of it with the help of the goals seek feature. By using the goal seek, you can identify the cause and effect of a scenario.

## How to Use Goal Seek Analysis in Excel

The Goal seek analysis can be utilized to get the required change for a certain result. That means if you have the result in your mind, you can get the change of the input value. Here, we take a dataset of an item that includes its price, quantity, and commission. Finally, we calculate the revenue of the item. Revenue is the product of item price, quantity, and commission.

We need to take our revenue from $3300 to $4500. For that reason, we would like to find out how many quantities of the item need to increase. In that case, goal seek analysis will come under consideration. First, go to the **Data** tab on the ribbon. Next, select **What-If Analysis** drop-down option from the **Forecast** group. After that, select the **Goal Seek** option.

Then the goal seek dialog box will appear. Set the revenue as **Set cell** and **$4500** in the **To value** section. To get this result, we would like to change the quantity of the item. Finally, click on **OK**.

As a consequence, you will see the quantity changes with the change in revenue. That’s the benefit you will get by using goal seek analysis.

## 3 Suitable Examples to Perform Goal Seek Analysis in Excel

To perform a goal seek analysis in Excel, we would like to show three suitable examples through which you can do the job. All of these methods are fairly easy to use. The main purpose of the goal seek analysis is that it helps to change the input value according to the change in result. In these three examples, we will try to give a complete overview.

### 1. Using Goal Seek for Average Age

Our first example is based on the goal seek analysis for average age. Here, we set a final average age. Then, using the goal seek analysis, we will get the change in input values. Follow the steps carefully.

**Steps **

- First, calculate the average using the available dataset.
- Select cell
**C12**. - Then, write down the following formula using
**the AVERAGE function**.

`=AVERAGE(C5:C10)`

- Press
**Enter**to apply the formula.

- Then, go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option. - Then, select
**Goal Seek**from the**What-If Analysis**drop-down option.

- As a result, the
**Goal Seek**dialog box will appear. - Put cell
**C12**in the**Set Cell**section. - Put
**30**in the**To value**section. - Set cell
**C10**in the**By changing cell**section.

- After that, we get the
**Goal Seek Status**dialog box which denotes that they get a solution. - Click on
**OK**to apply the change. - In the dataset, you will find the change in the
**Average**and input value of a certain cell.

**Read More: How to Use Goal Seek to Find an Input Value (3 Easy Examples)**

### 2. Calculate Annual Interest Rate from EMI

Our second example is based on calculating the annual interest rate from EMI. In this scenario, we have a loan amount to pay. We also know the duration to pay the loan. Finally, we can calculate the monthly payment or EMI by using **the PMT function**. To understand the example, follow the steps.

**Steps**

- First, select cell
**C8**. - Write down the following formula to get the monthly payment.

`=PMT(C5/12,C6*12,C4)`

** **

- Then, press
**Enter**to apply the formula.

- Then, go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option. - Then, select
**Goal Seek**from the**What-If Analysis**drop-down option.

- As a result, the
**Goal Seek**dialog box will appear. - Put cell
**C8**in the**Set Cell**section. - Put
**-120**in the**To value**section, - Set cell
**C5**in the**By changing cell**section.

- Then click on
**OK**. - After the calculations, Excel will show the
**Goal Seek Status**Click on**OK**to finish the process.

- As a consequence, you will find the following result.
- The annual interest rate changes with the change in the monthly payments.

**Note:**Here, the monthly payments or EMI is negative because it means you have to pay the money on monthly basis to the bank.

### 3. Using Goal Seek for Exam Marks

Our third example is based on the exam marks. Here, we take a dataset that calculates the final grade of a student using several students and their marks in the exam. Using the **Goal Seek **analysis, we set a final grade of a student and then change the input value according to the final grade. Before doing anything, we need to set the weight of each exam because each exam carries different values.

**Steps**

- First, we need to calculate the final grade of each student using exam marks and the weight of each exam.
- Select cell
**G5**. - Then, write down the following formula in the formula box.

`=0.25*B5+0.25*C5+0.25*D5+0.15*E5+0.1*F5`

- Press
**Enter**to apply the formula.

- After that, drag the
**Fill Handle**icon down the column.

- Then, go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option. - Then, select
**Goal Seek**from the**What-If Analysis**drop-down option.

- As a result, the
**Goal Seek**dialog box will appear. - Put cell
**G5**in the**Set Cell**section. - Put
**80**in the**To value**section. - Set cell
**B5**in the**By changing cell**section.

- After that, we get the
**Goal Seek Status**dialog box which denotes that they get a solution. - Click on
**OK**to apply the change. - In the dataset, you will find the change in the
**Final Grade**as**80**and the input value of**Exam 1**becomes**84**.

## How to Automate Goal Seek in Excel

We can easily **automate goal seek in Excel** by using the VBA code. If we write down a perfect code in VBA, we will get the goal seek automatically. In section will focus on how to automate goal seek in Excel. To show the whole method, we take a dataset that includes the item price, quantity, commission, and revenue. We need to set a target revenue. Then the VBA code will automate the goal seek.

Before doing anything, enable the **Developer** tab on the ribbon using the link **How to Show the Developer Tap on the Ribbon**.

**Steps**

- First, go to the
**Developer**tab on the ribbon. - Then, select the
**Visual Basic**option from the**Code**group.

- It will open up the
**Visual Basic**window. - Then, go to the
**Insert**tab at the top. - After that, select the
**Module**section.

- As a result, a
**Module**code window will appear. - Write down the following code.

```
Sub AutomateGoalSeek()
On Error GoTo Errorhandler
Worksheets("Automate Goal Seek").Activate
With ActiveSheet.Range("C7")
GoalSeek Goal:=Range("C9"), ChangingCell:=Range("C5")
End With
Exit Sub
Errorhandler: MsgBox ("Error! Invalid Value")
End Sub
```

- Then, close the
**Visual Basic**window. - After that, set the total revenue as
**$9000**in cell**C9**.

- Then, go to the
**Developer**tab on the ribbon. - Select the
**Macros**option from the**Code**group.

- Then, the
**Macro**dialog box will appear. - Select
**AutomateGoalSeek**from the**Macro name**section. - After that, click on
**Run**.

- As a result, you will see a change in quantity to get the target revenue.
- That’s the benefit to automate goal seek analysis in Excel.

**🔎 VBA Code Explanation:**

`Sub AutomateGoalSeek()`

First of all, provide a name for the sub-procedure of the macro.

`On Error GoTo Errorhandler`

Secondly, in case of an error, we’re setting an Errorhandler.

`Worksheets("Automate Goal Seek").Activate`

Thirdly, we’re activating our “VBA Manual” Worksheet.

```
With ActiveSheet.Range("C7")
GoalSeek Goal:=Range("C9"), ChangingCell:=Range("C5")
End With
Exit Sub
```

After that, we’ve put the Goal Seek criteria using a With Statement. Then, we set our target value in cell C9, set cell C5, and by changing cell C5.

```
Errorhandler: MsgBox ("Error! Invalid Value")
End Sub
```

If they don’t get the required result, it will return an invalid value in the message box.

Finally, end the sub-procedure of the macro.

## How to Do Goal Seek for Multiple Cells in Excel

You can do **goal seek for multiple cells**. To do this, you need to utilize the VBA code. Using the VBA code, you can change multiple cell results and change the input value for those results. To show this process, we take a dataset that includes weeks, project phases, scheduled hours, worked hours, and project progress. To understand the process carefully, follow the steps.

**Steps **

- First, go to the
**Developer**tab on the ribbon. - Then, select the
**Visual Basic**option from the**Code**group.

- It will open up the
**Visual Basic**window. - Then, go to the
**Insert**tab at the top. - After that, select the
**Module**option.

- As a result, a
**Module**code window will appear. - Write down the following code.

```
Sub GoalSeekMultipleCells()
For p = 5 To 13
Cells(p, "F").GoalSeek Goal:=0.5, ChangingCell:=Cells(p, "E")
Next p
End Sub
```

- Then, close the
**Visual Basic**window. - hen, go to the
**Developer**tab on the ribbon. - Select the
**Macros**option from the**Code**group.

- Then, the
**Macro**dialog box will appear. - Select
**GoalSeekMultipleCells**from the**Macro name**section. - After that, click on
**Run**.

- In this segment, we try to change the project progress value, and the worked hours value will change using the goal seek. See the screenshot.
- This process helps you to do goal seek for multiple cells condition.

**🔎 VBA Code Explanation:**

`Sub GoalSeekMultipleCells()`

First of all, provide a name for the sub-procedure of the macro

```
For p = 5 To 13
Cells(p, "F").GoalSeek Goal:=0.5, ChangingCell:=Cells(p, "E")
Next p
```

Next, we use a for loop for cell **5 **to cell **13**. In column** F**, we change the value from cell **F5** to cell **F13** and set **50%**. For these changes, column **E** will change its values accordingly like the usual goal seek analysis.

`End Sub`

Finally, end the sub-procedure of the macro.

## Keyboard Shortcut to Open Goal Seek in Excel

In a normal case, we put the mouse cursor on the output value which we want to change. Then, we go to the **Data** tab on the ribbon. After that, select **What-If Analysis** drop-down option from the **Forecast** group. Then, select **Goal Seek** from the **What-If Analysis** drop-down option. But you can open the **goal seek in Excel** using the keyboard shortcut. In this case, put the mouse cursor on the output value which we want to change. Then press **Alt+A+W+G**. As a result, the goal seek dialog box will appear.

## What to Do If Goal Seek Does Not Work in Excel?

The goal seek feature can fail to deliver the required result for many different cases. In this section, we will try to show the possible reason for this. Follow it carefully.

- Check the goal-seeking parameters: the supposed output cell must contain a formula and it should depend on the supposed input cell.
- Try to avoid circular references in the formula as well as the goal-seeking parameters.
- Another problem can arise due to iteration problems. The goal seek can’t just find values for every possible formula in Excel. This is because the feature uses a trial and error approach instead of rearranging the formula to solve values.

## Conclusion

To understand the goal seek analysis in Excel, we have shown three different examples through which you can do the job. We discussed the possible advantages and how to automate the goal seek in Excel. We have shown the possible keyboard shortcut for opening goal seek and also given the possible reason why the goal seek can’t work in certain conditions. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our **Exceldemy** page.