If you know the result of a formula, but you are not sure which input value the formula needs to get that result, you can use the **Goal Seek Command**. In this article, I will explain how to **use the Goal Seek feature **in Excel.

**Table of Contents**hide

## Download Practice Workbook

## What Is Goal Seek in Excel?

**Goal Seek** is a feature of the **What-If Analysis **Command **in Excel**. This feature helps to find a specific input value for the desired output from a formula. You can solve many real-life problems by using this feature. This feature uses the trial and error method to back-calculate the input value.

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

## Parameters of Goal Seek

There are **3** parameters in the **Goal Seek** dialog box that are needed to be specified to get the result. They are:

**1. Set cell:** In the **Set cell **parameter, you have to select the cell that contains the formula. This is the cell where you want to find the targeted value.

**2. To value:** In this parameter, you will have to enter the value you want as the output of the formula.

**3. By changing cell: **In this parameter, you will have to select the cell where you want to change or find the input value.

## 5 Suitable Examples to Use Goal Seek in Excel

In this article, I will explain** 5** suitable examples of how to** use Goal Seek **in Excel. You can use the **Goal Seek** feature for various purposes. In the following picture, you can see a dataset for a mortgage problem. I will explain how you can solve this problem using **Goal Seek** in the first example.

### Example-01: Solving Mortgage Problem in Excel by Using Goal Seek

Suppose that you need to borrow **$30,000** from a bank for** 3** years. The annual **Interest Rate** is **15%**. You can model this problem into the formula sown below to find the **Monthly Payment**.

`Monthly Payment = PMT(Interest Rate/12, Mortgage Term Length in Months, Mortgage Amount)`

Here, for this example, you can write the following formula in cell **C9 **to find the **Payment**.

`=PMT(C7/12,C6,C5)`

By using this formula, I calculated the **Payment** amount. The **Payment** amount is **$1,040**.

Now, if your maximum** Payment** is limited to** $1,000** per month, how high an annual **Interest Rate** can you tolerate?

Let me explain how you can **use Goal Seek** in Excel to solve the problem.

**Steps:**

- Firstly, go to the
**Data**tab. - Secondly, select
**What-If Analysis**.

After that, a drop-down menu will appear.

- Thirdly, select
**Goal Seek**from the drop-down menu.

Now, a dialog box named** Goal Seek **will appear.

- Firstly, select the
**Set cell**. Here, I selected cell**C9**because this cell contains the formula for**Payment**. - Secondly, enter
**To value**. Here, I wrote**-1000**because the maximum**Payment**is limited to**$1’000**. And, as you are making a**Payment**the**sign is negative**. - Thirdly, select By changing cell. Here, I selected cell
**C7**because this cell contains the**Interest Rate**. - Finally, select
**OK**.

Here, another **dialog box** named** Goal Seek Status** will appear. This **dialog box** displays the **Target value** and the **Currant value**.

- Next, select
**OK**.

Finally, you can see that I have found the highest annual **Interest Rate** you can tolerate if your maximum monthly **Payment** limit is **$1,000**.

### Example-02: Use of Goal Seek to Find Unknown Value in Excel

In this example, I will explain how to solve an algebra problem to find an **unknown value** by the use of the **Goal Seek** feature in Excel.

Most story problems in algebra require you to choose a variable (which is usually called x) to solve a particular equation. This is another situation in which you use the Excel** Goal Seek** feature.

Suppose that you are buying **40** pounds of candy. There are two types of candy. **Candy A **sells for **$10** per pound, and** Candy B **sells for **$6 **per pound. How many pounds of **Candy A** you can buy to result in an **Average Cost** of** $8** per pound?

Let’s connect this situation with an equation first. **Variable (x)** represents the pounds of **Candy A **you can buy. And,** (40 – x)** represents the pounds of **Candy B** you can buy. Formula **x*10 + (40 – x)*6** can tell how much money you will have to pay for these candies. By diving the formula by **40**, you can get the **Average Cost**. In summary, the equation will be:

`(x*10 + (40 – x)*6)/40 = 8`

Let’s see how you can solve this problem.

**Steps:**

To begin with, I will insert the formula for **Average Cost** in my desired cell.

- Firstly, select the cell where you want to calculate the
**Average Cost**. Here, I selected cell**C11**. - Secondly, in cell
**C11**write the following formula.

`=(C7*C8+(40-C7)*C9)/40`

Here, the formula will subtract the value in cell **C7** from **40** and then **multiply** it by the value in cell **C9**. Then, the value in cell **C7** will be **multiplied** by the value in cell **C7**. And, now both of these values will be **summed** to get the total cost. Then, the formula will divide the result by **40** and return the **Average Cost**.

- Thirdly, press
**ENTER**to get the result.

Now, the **Average Cost** is not showing the right value as you have not entered any value in cell **C7** yet. Here, I will explain how you can find out the pounds of **Candy A** you need to buy to get an **Average Cost** of **$8** by using the **Goal Seek **feature in Excel.

- Firstly, go to the
**Data**tab. - Secondly, select
**What-If Analysis**.

After that, a drop-down menu will appear.

- Thirdly, select
**Goal Seek**from the drop-down menu.

Here, the **Goal Seek** dialog box will appear.

- Firstly, select the
**Set cell**. Here, I selected cell**C11**because this cell contains the formula for the**Average Cost**. - Secondly, enter
**To value**. Here, I wrote**8**because the expected**Average Cost**is**$8**. - Thirdly, select
**By changing cell**. Here, I selected cell**C7**because this cell contains**Candy A (Pounds)**. - Finally, select
**OK**.

Now, another **dialog box** named** Goal Seek Status** will appear. This **dialog box** displays the **Target value** and the **Currant value**.

- Next, select
**OK**.

Finally, you can see that I have found how many **Pounds of Candy A **you will need to buy to make the average cost **$8**.

### Example-03: Applying Goal Seek to Find Breakeven Point

In this example, I will explain how to use **Goal Seek** in Excel to find the **breakeven point**.

Supposed, you are managing a conference at your college. Your** Fixed Cost** is **$15,000**. You must pay the **10** speakers **$700** each and the college union **$300** per conference for food and lodging costs. You are charging each conference participant who is not a speaker **$900**, which includes the conference fee and food and lodging costs. How many paid registrants need to attend for you to break even?

To break even, your **Profit** should be equal to **0**. The **Profit** is equal to gross income minus your **Total cost**. Therefore, we can write a formula in this way:

`Profit = Number of non-speaker * $900 – ($15,000 + $300 + 10 * $700)`

Let’s see how you can get the **Numbers of non-speaker** by using the **Goal Seek **feature.

**Steps:**

To begin with, I will insert the formula for **Profit** into the Excel sheet.

- Firstly, select the cell where you want to calculate the
**Profit**. - Secondly, write the following formula in the selected cell.

`=C9*C10-C5-C6-C7*C8 `

Here, the value in cell **C9 **is **multiplied **by the value in cell **C10**. Then, the value in cell **C5** and cell **C6** is** subtracted** from the result. And, then the value in cell **C7** is **multiplied** by the value in cell **C8** and **subtracted **from the previous result, and returns the** Profit**.

- Thirdly, press
**ENTER**to get the**Profit**.

Here, the **Profit** is** -$22,300** because you do not have any gross income yet as you have not entered the **Number of non-speaker**.

- Now, go to the
**Data**tab. - Next, select
**What-If Analysis**.

After that, a drop-down menu will appear.

- Then, select
**Goal Seek**from the drop-down menu.

Here, the **Goal Seek** dialog box will appear.

- Firstly, select the
**Set cell**. Here, I selected cell**C12**because this cell contains the formula for the**Profit**. - Secondly, enter
**To value**. Here, I wrote**0**because the**Profit**is**0**at the breakeven point. - Thirdly, select
**By changing cell**. Here, I selected cell**C10**because this cell contains the**Number of non-speakers**. - Finally, select
**OK**.

After that, another **dialog box** named** Goal Seek Status** will appear. This **dialog box** displays the **Target value** and the **Currant value**.

- Next, select
**OK**.

Finally, I got the **Number of non-speakers **needed for you to reach the breakeven point by using **Goal Seek** and, it is **25**.

**Read More: How to Create Financial Planning Calculator in Excel**

### Example-04: Solving Equation in Excel by Using Goal Seek

In this example, I will show you how you can use** Goal Seek **in Excel to **solve an equation**.

To explain this example, I have taken the following **equation**.

`x`

^{2}`+10x+25 = 0 `

Here, I will solve the **equation** and find the value of the **Variable (x)**.

Let’s see the steps.

**Steps:**

To begin with, I will insert the formula for this equation into the Excel sheet.

- Firstly, select the cell where you want the
**Solution**of the equation. Here, I selected cell**C7**. - Secondly, in cell
**C7**write the following formula.

`=C5^2-10*C5+25`

Here, the formula will** raise **the value in cell **C5** which is the **x **to the** power** of **2**. Then, **multiply** the value in cell **C5** by **10** and then **subtract** it from the previous result. After that, it will add **25** with the result and return the **Solution** of the equation.

- Thirdly, press
**ENTER**to get the result.

Here, the result is** 25** as I have not entered any value for the** Variable (x)**.

- Now, go to the
**Data**tab. - Next, select
**What-If Analysis**.

After that, a drop-down menu will appear.

- Then, select
**Goal Seek**from the drop-down menu.

Here, the **Goal Seek** dialog box will appear.

- Firstly, select the
**Set cell**. Here, I selected cell**C7**because this cell contains the formula for the**Solution**of the equation. - Secondly, enter
**To value**. Here, I wrote**0**because the**Solution**is**0**in the given equation. - Thirdly, select
**By changing cell**. Here, I selected cell**C5**because this cell contains the**Variable (x)**. - Finally, select
**OK**.

After that, another **dialog box** named** Goal Seek Status** will appear. This **dialog box** displays the **Target value** and the **Currant value**.

- Next, select
**OK**.

Finally, you can see that I have found the value of the** Variable (x)** by using the **Goal Seek **feature in Excel.

### Example-05: Using Goal Seek in Excel for Effective Interest Rate

In this example, I will explain how to use **Goal Seek **for **Effective Interest Rate**.

The** Effective Interest Rate** is the interest rate paid by someone when the effect of the compounding period is considered. The formula for **Effective Interest Rate** is shown below.

`Effective Interest Rate = (1+i/n)`

^{n}`-1`

Where,

**i = Nominal Interest Rate**

**n = Compounding Periods Per Year**

Suppose, you have a Nominal Interest Rate of **10% **and the **Compounding Periods Per Year** is **12**. What are the **Effective Interest Rate **and the number of **Compounding Periods Per Year** if you want your **Effective Interest Rate** to be **10.2%**?

Let’s see how you can solve this problem.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Effective Interest Rate**. Here, I selected cell**C8**. - Secondly, in cell
**C8**write the following formula.

`=((1+C5/C6)^C6)-1`

Here, I **divided** the value in cell **C5** by the value in cell **C6** and then **added 1 **with the result. Then, **raised** the result to the **power **of the value in cell **C6**. After that, I **subtracted 1 **from the result and the formula will return the **Effective Interest Rate**.

- Finally, press
**ENTER**to get the result.

Now, I will use the **Goal Seek** feature to find the **Compounding Periods Per Year** needed for the **Effective Interest Rate** to be **10.2%**.

- Firstly, go to the
**Data**tab. - Secondly, select
**What-If Analysis**.

After that, a drop-down menu will appear.

- Thirdly, select
**Goal Seek**from the drop-down menu.

Here, the **Goal Seek** dialog box will appear.

- Firstly, select the
**Set cell**. Here, I selected cell**C8**because this cell contains the formula for the**Effective Interest Rate**. - Secondly, enter
**To value**. Here, I wrote**.102**because the expected**Effective Interest Rate**is**10.2%**. - Thirdly, select
**By changing cell**. Here, I selected cell**C6**because this cell contains the**Compounding Periods Per Year**. - Finally, select
**OK**.

Now, another **dialog box** named** Goal Seek Status** will appear. This **dialog box** displays the **Target value** and the **Currant value**. Here, the Current Value does not match exactly with the Target Value but it is close and I am satisfied with the Current Value.

- Next, select
**OK**.

Finally, I have found the **Compounding Periods Per Year **for the desired **Effective Interest Rate**.

## How to Use Goal Seek in Excel For Multiple Cells

In this section, I will explain how to use** Goal Seek** in Excel **for** **multiple cells**. The **Goal Seek **feature from **What-If Analysis** can not be used for multiple cells at the same time. **VBA** code is needed to do this type of operation.

For this example, I have taken the following dataset. This dataset contains **Product** names, their **Total Cost**, and **Selling Price**. I will calculate the **Profit Percentage **for every **Product** and use **Goal Seek **for multiple cells to find the **Selling Price** to get a **Profit Percentage **of **35%**.

Let me show you how you can solve this problem.

**Steps:**

- Firstly, select the cell where you want to calculate the
**Profit Percentage**. Here, I selected cell**E5**. - Secondly, in cell
**E5**write the following formula.

`=((D5-C5)/C5)*100% `

Here, I **subtracted** the value in cell **C5 **which is the **Total Cost **from the value in cell **D5 **which is the **Selling Price**. And, then **divided** the result by the value in cell **C5**. Then, I **multiplied** the result by **100%** and now the formula will return the **Profit Percentage**.

- Thirdly, press
**ENTER**to get the result.

- Now, drag the Fill Handle to copy the formula to the other cells.

Here, you can see that I have copied the formula and got the **Profit Percentage **for each **Product**.

Now, I will show you how you can use **Goal Seek **for **multiple cells** by using Excel** VBA**. Here, I will find out the **Selling Price** for every** Product** if I want the **Profit Percentage** to be** 35%**.

- Firstly, go to the
**Developer**tab. - Secondly, select
**Visual Basic**.

After that, the **Visual Basic** editor window will open.

- Next, select the
**Insert**tab. - Then, select
**Module**.

Here, a **module** will open.

- Now, write the following code in that
**module**.

```
Sub Goal_Seek_Multi_Cells()
For row_no = 5 To 9
Worksheets("Goal Seek for Multiple Cells").Cells(row_no, "E").GoalSeek Goal:=0.35, _
ChangingCell:=Worksheets("Goal Seek for Multiple Cells").Cells(row_no, "D")
Next row_no
End Sub
```

**Code Breakdown**

- Here, I created a
**Sub Procedure**named**Goal_Seek_Multi_Cells**. - Then, I used a
**For Next Loop**to go through multiple cells in a column. - Next, I used the
**GoalSeek**method and set the**Goal**and**ChangingCell**according to the need. The**GoalSeek**method will change the value in**ChangingCell**to achieve the**Goal**in the selected cell. - Finally, I ended the
**Sub Procedure**.

Now, **Save** the code and go back to the worksheet.

- Firstly, go to the
**Developer**tab. - Secondly, select
**Macros**.

After that, a **dialog box** named **Macro** will appear.

- Firstly, select the
**Macro name**. - Secondly, select
**Run**.

Finally, you will see the **Selling Price** of every product has changed accordingly to get a **Profit Percentage **of **35%**.

**Read More: How to Automate Goal Seek in Excel (2 Easy Methods)**

## Goal Seek Precision in Excel

In this section, I will show you how you can get more **precise** results by using the **Goal Seek** feature. Here, I will use the dataset from **Example-04** to explain this section. Let’s see the steps.

**Steps:**

- Firstly, go to the
**File**tab.

- Secondly, select
**Options**.

Now, a **dialog box** named **Excel Options** will appear.

- Firstly, go to the
**Formulas**tab. - Secondly, decrease the value for
**Maximum Change**to get a more precise result. - Thirdly, select
**OK**.

Now, go back to the worksheet.

- Firstly, go to the
**Data**tab. - Secondly, select
**What-If Analysis**. - Thirdly, select
**Goal Seek**.

Here, the **Goal Seek** dialog box will appear.

- Firstly, select the
**Set cell**. Here, I selected cell**C8**because this cell contains the formula for the**Effective Interest Rate**. - Secondly, enter
**To value**. Here, I wrote**.102**because the expected**Effective Interest Rate**is**10.2%**. - Thirdly, select
**By changing cell**. Here, I selected cell**C6**because this cell contains the**Compounding Periods Per Year**. - Finally, select
**OK**.

Now, another **dialog box** named** Goal Seek Status** will appear. This **dialog box** displays the **Target value** and the **Currant value**. Here, the **Current Value **matches the **Target Value** and I have achieved a more precise result than before.

- Next, select
**OK**.

Finally, you can see that I have found the **Compounding Periods Per Year** for my desired **Effective Interest Rate**.

## Things to Remember

- It should be noted that the cell you are selecting for
**By changing cell**parameter must contain a value. If the cell contains a formula then you will get an error. - Whenever working with
**VBA**, you must save the Excel file as**Excel Macro-Enabled Workbook**. Otherwise, the macros won’t work.

## Practice Section

Here, I have provided a practice sheet for you to practice how to **use Goal Seek **in Excel.

## Conclusion

To conclude, I tried to cover how to **use Goal Seek** in Excel. Here, I explained **5 **suitable examples of using the **Goal Seek **feature in Excel. I hope this article was clear to you. If you have any questions, feel free to let me know in the comment section below.