How to Use Goal Seek in Excel (5 Suitable Examples)

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.


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.


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.

How to Use Goal Seek in Excel


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.

Solving Mortgage Problem in Excel by Using Goal Seek

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.

Selecting What-If Analysis Command to use Goal Seek in Excel

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.

Read more: Goal Seek Analysis in Excel (Detailed Analysis)


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

Use of Goal Seek to Find Unknown Value in Excel

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

Inserting Formula to Use Goal Seek in Excel

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.

Goal Seek Dialog Box in Excel

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.

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


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 regis­trants need to attend for you to break even?

Applying Goal Seek to Find Breakeven Point

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 

Formula for Profit to Use Goal Seek in Excel

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.

x2+10x+25 = 0   

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

Solving Equation in Excel by Using Goal Seek

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

Using Goal Seek in Excel for Effective Interest Rate

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

How to Use Goal Seek in Excel For Multiple Cells

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.

Opening Visual Basic Window to Use Goal Seek in Excel for Multiple Cells

After that, the Visual Basic editor window will open.

  • Next, select the Insert tab.
  • Then, select Module.

Inserting Module to Write VBA Code to Use Goal Seek in Excel

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

VBA Code for Goal Seek in Excel for Multiple Cells

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.

Running Macros to Use Goal Seek for Multiple Cells in Excel

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


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.

Goal Seek Precision

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

Practice Sheet for 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.

Zhiping Yan

Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo