The goal seek is a handy feature in Microsoft Excel that helps us measure an input based on the desired output. It can be very useful in financing and forecasting applications. Up to the latest version of Excel, it is a part of the what-if analysis tool. This tutorial will shed light on what is goal seek and how to use it from what if analysis in Microsoft Excel.
What Is Goal Seek in Excel?
The goal seek function in Excel is a method of finding out a supposed input value for the desired output value. In other words, you have to have an output first to get one of the primary input functions. Excel uses trials and errors for this operation. Let’s take a look at the following example.
The area is determined by 0.5*base*height. But if we want an area of say 9 and we have the flexibility of changing the height to our liking, goal seek comes into play. Of course, one way to determine is to work with the mathematical equation and find the desired height to make the area 9 square units. But with Excel’s goal seek feature we can directly find the desired height to be as follows.
More on how to do this in the following examples.
Read More:Â How to Use Goal Seek in Excel
Benefits of Using Goal Seek in Excel
The main benefit of using Goal Seek is to measure the 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.
While it is one of the useful scenarios the goal seek can be used for, the feature is not limited to it. You can practically backtrack anything that can be calculated in Excel.
How to Do What-If Analysis Using Goal Seek in Excel: 3 Suitable Examples
In this section, we will go over specific examples and how to use the goal seek feature from the what-if analysis in Excel. We will be using different datasets for each example. So these will be introduced in their own sub-sections. But the basic idea is all the same. You can find the feature in the Data tab under the What-If Analysis and choose the desired value for the desired cell and the changeable cell parameter. Follow the examples below for more details.
Example 1: Calculating Original Price from Increased Price
In the first example, we are going to use the following dataset.
The third row is calculated by the original price*(1+%increase). Now let’s assume, we have to add another row where the increase is 12% and the increased price follows the same formula.
We are going to calculate what the original price should be in order to make the increased price 2000$ with the help of the goal seek feature of what-if analysis in Excel.
Steps:
- First of all, go to the Data tab on your ribbon.
- Then select What-If Analysis from the Forecast group.
- After that, select Goal Seek from the drop-down.
- As a result, the Goal Seek box will appear. Now select cell D8 for the Set cell field.
- Next, enter 2000 in the To value field.
- Then put the cell value B8 for the By changing cell field.
- Finally, click on OK.
- After the trial and error calculations, the Goal Seek Status box will appear. Click OK on it.
The dataset will now look like the following with the required value already inserted.
This is how you can measure the original price from the increased price with the help of the goal seek feature from the what-if analysis in Excel.
Example 2: Calculating Required Age from Average Age
Let’s look at another example, where we need to find a component value to match a certain average.
The average is calculated here by the following formula consisting of the AVERAGE function.
=AVERAGE(C5:C8)
Now let’s consider a scenario, where we need the average age of these people to be 30. With the help of the goal seek feature from what if analysis, we will now measure what the fourth person’s age should be to make the average age 30.
Steps:
- First of all, go to the Data tab on your ribbon.
- Then select What-If Analysis from the Forecast group.
- After that, select Goal Seek from the drop-down menu.
- Now in the Set cell field of the Goal Seek box, select cell C10.
- Then enter 30 in the To value field.
- And select cell C8 the By changing cell field.
- After that, click on OK.
- After completing the calculations, Excel will display the Goal Seek Status Finally, click OK on it.
The dataset will look like this. And we can see the required age for the fourth person from here is 31.
This is another scenario where you can use the goal-seek feature of the what-if analysis in Excel.
Example 3: Calculating Annual Interest Rate from EMI
Let’s look at another scenario—and a very useful one. Say, we have an amount of loan in our hand and we can pay it off in a fixed amount of time. But we will only be able to make a fixed deposit each month. We are going to measure what interest we should seek for the loan.
Take a look at the following dataset.
The monthly payment here is calculated using the PMT function.
=PMT(C5/12,C6*12,C4)
But let’s say, we want to pay a monthly payment of 85$. We will now calculate the interest rate we should seek for with the help of goal seek from the what-if analysis in Excel.
Steps:
- First, go to the Data tab on your ribbon.
- Then select What-If Analysis from the Forecast group.
- Now select Goal Seek from the drop-down menu.
- After that, select cell C8 for the Set cell field.
- In the To value field, enter -85.
- Next, select cell C5 for the By changing cell field.
- Then click on OK.
- After the calculations, Excel will show the Goal Seek Status Click on OK to finish the process.
The spreadsheet will now look like this.
This way, you can calculate the annual interest rate if the monthly payment is predetermined with the help of the goal seek from the what-if analysis in Excel.
More About Goal Seek
There are some limitations while using this feature. The goal-seek feature 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.
Let’s take a look at the following example.
Here, a value is calculated through a fraction where a denominator contains the variable with a subtraction formula. Now Try to make the output value to 25 by adding these inputs in the Goal Seeking box.
After clicking on OK, it will say the goal seek may not have found the answer.
But for this case particularly, you can choose a value greater than 20 to start with in the input.
Now open up the Goal Seek box and enter the following values in the field.
After completing the process, you can now find the required input.
Read More: How to Automate Goal Seek in Excel
What to Do If Goal Seek Does Not Work in Excel?
The goal seek feature may fail to deliver results for many different cases. One has been shown in the section above which may occur for the iteration problems. For other problems, you can try out these solutions-
- 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.
Download Practice Workbook
You can download the workbook used for the demonstration from the link below.
Conclusion
This concludes our discussion on the goal seek feature of what-if analysis from Excel. Hopefully, you can use this feature with ease for your purposes. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
