Excel’s Goal Seek feature is a powerful tool that allows users to find the input value required to achieve a specific outcome. Whether you’re trying to pay off a loan, calculate a break-even point, or determine the ideal selling price for a product, Goal Seek can help you quickly find the answer you need. This tutorial will walk you through the steps to use Goal Seek in Excel. We will cover how to set up your worksheet, define your goal and input values, and use the Goal Seek tool to find the desired input value. By the end of this tutorial, you will be able to use Goal Seek to solve a variety of optimization problems and make more informed decisions based on your data.
This Tutorial Covers:
- What is Goal Seek in Excel
- Examples of using Goal Seek in Excel
- Example 1- Calculate the target quantity to sell to obtain a certain revenue
- Example 2- Calculate the subject’s mark to obtain a certain total exam mark
- Example 3- Calculate the number of people who will be invited to spend a specific budget
- The Rules Governing the Parameters
- More about Goal Seek
- When goal seek cannot solve
1. What is Goal Seek in Excel?
Goal Seek is a feature in Microsoft Excel that allows users to find the input value needed to achieve a specific goal or output value. It is a tool used for optimization, where users can set a desired outcome and then have Excel calculate the input value required to reach that outcome.
For example, suppose you want to pay off a loan of $10,000 in five years with an interest rate of 6% per year. Using Goal Seek, you can find out how much you need to pay each month to achieve that goal. You can set the target value as the total amount paid over the five-year period and then use Goal Seek to determine the monthly payment required to achieve that goal.
To use Goal Seek in Excel, you need to have a target cell that contains a formula, and at least one input cell that affects the target cell. You then specify the target value you want to achieve and which input cell you want to adjust to reach that goal. Excel will then automatically adjust the input value until the target value is met.
2. Examples of using Goal Seek in Excel:
Every time you build a formula or function in Excel, you combine different elements to calculate a result. In contrast, Goal Seek operates by letting you start with the intended outcome and calculating the input value that will produce that outcome. To demonstrate how to use Goal Seek, we’ll use a few instances.
- Example 1- Calculate the target quantity to sell to obtain a certain revenue:
Suppose you have a dataset like below. You want to figure out how many Apple Watches you need to sell in order to make $14,000 in revenue.
The steps to calculate the target quantity to sell to obtain a certain revenue are described below:
Step 1: Apply the following formula to cell E2.
=B2*C2
Step 2: Go to the “Data” tab on the Excel ribbon. Click on “What-If Analysis” and select “Goal Seek” from the dropdown menu.
Step 3: The “Goal Seek” window then appears. In the “set cell” box, type $E$2. The revenue cell E2 is the target cell. In the “to value” box, type 14000. This is the desired value for the “set cell.” In the “by changing cell,” enter $C$2. Cell C2 must be used to calculate the amount. So, in order to reach the specified revenue, this cell needs to be modified. Press “Ok.”
Step 4: The following picture shows the result. You must therefore sell 20 apple watches in order to make $14,000 in revenue. To make the changes effective, click “Ok.”
- Example 2- Calculate the subject’s mark to obtain a certain total exam mark:
Assume you have a dataset in Excel that includes the marks obtained in each subject. You want to determine the minimum mark you need in Biology to achieve a total exam mark of 450.
The steps to calculate the subject’s mark to obtain a certain total exam mark:
Step 1: Apply the following formula to cell D2.
=SUM(B2:B6)
Step 2: Go to the “Data” tab on the Excel ribbon. Click on “What-If Analysis” and select “Goal Seek” from the dropdown menu.
Step 3: The “Goal Seek” window then appears. In the “set cell” box, type $D$2. In the “to value” box, type 450. This is the desired value for the “set cell.” In the “by changing cell,” enter $B$6. Cell B6 must be used to calculate the mark. So, in order to reach the specified total marks, this cell needs to be modified. Press “Ok.”
Step 4: The following picture shows the result. You must obtain 85 marks in Biology to achieve a total exam mark of 450. To make the changes effective, click “Ok.”
- Example 3- Calculate the number of people who will be invited to spend a specific budget:
Suppose you have an Excel dataset that contains the cost per person for each section of your party. You want to calculate the maximum number of people you can invite to the party without exceeding your budget of $1,000.
Step 1: Apply the following formula to cell F2.
=D2*D4
Step 2: Go to the “Data” tab on the Excel ribbon. Click on “What-If Analysis” and select “Goal Seek” from the dropdown menu.
Step 3: The “Goal Seek” window then appears. In the “set cell” box, type $F$2. In the “to value” box, type 1000. This is the desired value for the “set cell.” In the “by changing cell,” enter $D$4. Cell D4 must be used to calculate the number of people to be invited. So, in order to reach the specified total budget, this cell needs to be modified. Press “Ok.”
Step 4: The following picture shows the result. you can invite a maximum of 27 people with the available budget, you should invite 27 people to the party to avoid exceeding the budget of $1,000. To make the changes effective, click “Ok.”
3. The Rules Governing the Parameters:
When using the Goal Seek feature in Excel, there are a few rules to keep in mind:
- The “Set Cell” should always contain a formula. This formula represents the output you want to achieve by adjusting the input value.
- The formula of the “Set Cell” should depend, whether directly or indirectly, on the “By Changing Cell.” This is necessary to study the impact of the input value on the output value.
- There shouldn’t be any special characters in the “By Changing Cell”. This cell represents the input value you want to adjust to achieve the desired output.
- More about Goal Seek:
The following scenarios call for the usage of Excel’s Goal Seek feature:
- To determine the unknown input for a specified output (known)
- To determine how a change in the input (variable) will affect the result. (set cell)
- To undertake what-if analysis, which involves examining potential outcomes given various input values.
- To return the estimated value in the event that a solution to the issue cannot be discovered.
Note: Throughout, the Goal Seek analysis was conducted using the same formula. To investigate the impact on the output, the input values entered in the “by changing cell” box are altered.
- When Goal Seek cannot solve?
There are certain situations where Excel Goal Seek may not be able to solve a problem, including:
Nonlinear relationships: Goal Seek works by using iterative calculations to find a solution. If the relationship between the input and output values is highly nonlinear, the iterative process may not converge to a solution, or may converge to a local optimum rather than the global optimum.
Multiple solutions: If there are multiple combinations of input values that could produce the same output value, Goal Seek may not be able to determine which combination of values is the correct one.
Circular references: If the formulas in the worksheet contain circular references, meaning that they depend on each other in a way that creates a loop, Goal Seek may not be able to determine a solution.
Inconsistent or conflicting data: If the data in the worksheet is inconsistent or conflicting, such as if the formulas produce negative numbers or divide by zero, Goal Seek may not be able to determine a solution.
In general, Goal Seek works best when there is a clear relationship between the input and output values, and when the formulas in the worksheet are well-behaved and do not contain circular references or other issues that could prevent convergence. If you encounter problems with Goal Seek, it may be necessary to use other tools, such as Solver or VBA macros, to find a solution.
For ready-to-use Dashboard Templates:
