# How to Do What-If Analysis Using Goal Seek in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF