Goal Seek Analysis in Excel (Detailed Analysis)

What-if analysis is a process through which you can see the outcome by changing any cell in the dataset. In Microsoft Excel, you can get three different types of what-if analysis. The Goal Seek Analysis is one of them. This article will show the goal seek analysis in Excel. I hope you find this article informative and gain lots of knowledge.

Download Practice Workbook

Download the practice workbook below.

Overview of Excel What-If Analysis: Goal Seek

What-if analysis is a process through which you can see the outcome by changing any cell in the dataset. By using the what-if analysis in Excel, you can use several sets of values and get the desired outcome. For example, in the what-if analysis, you can calculate the total expenses of several cars and finally select your preferred car. That means, using the what-if analysis, you can establish a proper overview of all types of things and also denotes how it reacts in the future.

The main purpose of the what-if analysis in Excel is to determine the outcome in a statistical mood and do the risk assessment. The main advantage to use what-if analysis in Excel is that there is no need to create a new worksheet but we can get the final results for different inputs. There are three types of what-if analysis in Excel: Scenario, Goal Seek and Data Table.

The Goal Seek helps to measure the required amount of something beforehand if you have the desired result in your mind. That means if you have the result in your mind but don’t know how to change the input value to get the desired result. In that case, goal seek can help you. For example, you know what you want to get in the final result, but don’t know how much you need to score to touch that grade. Goal seek will help you to do that effectively.

Advantages of Using Goal Seek Analysis in Excel

The main advantage of using goal seek analysis is to measure a 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. By using the goal seek, you can identify the cause and effect of a scenario.

How to Use Goal Seek Analysis in Excel

The Goal seek analysis can be utilized to get the required change for a certain result. That means if you have the result in your mind, you can get the change of the input value. Here, we take a dataset of an item that includes its price, quantity, and commission. Finally, we calculate the revenue of the item. Revenue is the product of item price, quantity, and commission.

We need to take our revenue from $3300 to $4500. For that reason, we would like to find out how many quantities of the item need to increase. In that case, goal seek analysis will come under consideration. First, go to the Data tab on the ribbon. Next, select What-If Analysis drop-down option from the Forecast group. After that, select the Goal Seek option.

Then the goal seek dialog box will appear. Set the revenue as Set cell and $4500 in the To value section. To get this result, we would like to change the quantity of the item. Finally, click on OK.

As a consequence, you will see the quantity changes with the change in revenue. That’s the benefit you will get by using goal seek analysis.

3 Suitable Examples to Perform Goal Seek Analysis in Excel

To perform a goal seek analysis in Excel, we would like to show three suitable examples through which you can do the job. All of these methods are fairly easy to use. The main purpose of the goal seek analysis is that it helps to change the input value according to the change in result. In these three examples, we will try to give a complete overview.

1. Using Goal Seek for Average Age

Our first example is based on the goal seek analysis for average age. Here, we set a final average age. Then, using the goal seek analysis, we will get the change in input values. Follow the steps carefully.

Steps

• First, calculate the average using the available dataset.
• Select cell C12.
• Then, write down the following formula using the AVERAGE function.

• Press Enter to apply the formula.

• Then, go to the Data tab on the ribbon.
• Select What-If Analysis drop-down option.
• Then, select Goal Seek from the What-If Analysis drop-down option.

• As a result, the Goal Seek dialog box will appear.
• Put cell C12 in the Set Cell section.
• Put 30 in the To value section.
• Set cell C10 in the By changing cell section.

• After that, we get the Goal Seek Status dialog box which denotes that they get a solution.
• Click on OK to apply the change.
• In the dataset, you will find the change in the Average and input value of a certain cell.

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

2. Calculate Annual Interest Rate from EMI

Our second example is based on calculating the annual interest rate from EMI. In this scenario, we have a loan amount to pay. We also know the duration to pay the loan. Finally, we can calculate the monthly payment or EMI by using the PMT function. To understand the example, follow the steps.

Steps

• First, select cell C8.
• Write down the following formula to get the monthly payment.

• Then, press Enter to apply the formula.

• Then, go to the Data tab on the ribbon.
• Select What-If Analysis drop-down option.
• Then, select Goal Seek from the What-If Analysis drop-down option.

• As a result, the Goal Seek dialog box will appear.
• Put cell C8 in the Set Cell section.
• Put -120 in the To value section,
• Set cell C5 in the By changing cell section.

• Then click on OK.
• After the calculations, Excel will show the Goal Seek Status Click on OK to finish the process.

• As a consequence, you will find the following result.
• The annual interest rate changes with the change in the monthly payments.

3. Using Goal Seek for Exam Marks

Our third example is based on the exam marks. Here, we take a dataset that calculates the final grade of a student using several students and their marks in the exam. Using the Goal Seek analysis, we set a final grade of a student and then change the input value according to the final grade. Before doing anything, we need to set the weight of each exam because each exam carries different values.

Steps

• First, we need to calculate the final grade of each student using exam marks and the weight of each exam.
• Select cell G5.
• Then, write down the following formula in the formula box.

• Press Enter to apply the formula.

• After that, drag the Fill Handle icon down the column.

• Then, go to the Data tab on the ribbon.
• Select What-If Analysis drop-down option.
• Then, select Goal Seek from the What-If Analysis drop-down option.

• As a result, the Goal Seek dialog box will appear.
• Put cell G5 in the Set Cell section.
• Put 80 in the To value section.
• Set cell B5 in the By changing cell section.

• After that, we get the Goal Seek Status dialog box which denotes that they get a solution.
• Click on OK to apply the change.
• In the dataset, you will find the change in the Final Grade as 80 and the input value of Exam 1 becomes 84.

How to Automate Goal Seek in Excel

We can easily automate goal seek in Excel by using the VBA code. If we write down a perfect code in VBA, we will get the goal seek automatically. In section will focus on how to automate goal seek in Excel. To show the whole method, we take a dataset that includes the item price, quantity, commission, and revenue. We need to set a target revenue. Then the VBA code will automate the goal seek.

Before doing anything, enable the Developer tab on the ribbon using the link How to Show the Developer Tap on the Ribbon.

Steps

• First, go to the Developer tab on the ribbon.
• Then, select the Visual Basic option from the Code group.

• It will open up the Visual Basic window.
• Then, go to the Insert tab at the top.
• After that, select the Module section.

• As a result, a Module code window will appear.
• Write down the following code.

Sub AutomateGoalSeek()
On Error GoTo Errorhandler
Worksheets("Automate Goal Seek").Activate
With ActiveSheet.Range("C7")
GoalSeek Goal:=Range("C9"), ChangingCell:=Range("C5")
End With
Exit Sub
Errorhandler: MsgBox ("Error! Invalid Value")
End Sub

• Then, close the Visual Basic window.
• After that, set the total revenue as $9000 in cell C9.

• Then, go to the Developer tab on the ribbon.
• Select the Macros option from the Code group.

• Then, the Macro dialog box will appear.
• Select AutomateGoalSeek from the Macro name section.
• After that, click on Run.

• As a result, you will see a change in quantity to get the target revenue.
• That’s the benefit to automate goal seek analysis in Excel.

Sub AutomateGoalSeek()

On Error GoTo Errorhandler

Worksheets("Automate Goal Seek").Activate

With ActiveSheet.Range("C7")
GoalSeek Goal:=Range("C9"), ChangingCell:=Range("C5")
End With
Exit Sub

Errorhandler: MsgBox ("Error! Invalid Value")
End Sub

How to Do Goal Seek for Multiple Cells in Excel

You can do goal seek for multiple cells. To do this, you need to utilize the VBA code. Using the VBA code, you can change multiple cell results and change the input value for those results. To show this process, we take a dataset that includes weeks, project phases, scheduled hours, worked hours, and project progress. To understand the process carefully, follow the steps.

Steps

• First, go to the Developer tab on the ribbon.
• Then, select the Visual Basic option from the Code group.

• It will open up the Visual Basic window.
• Then, go to the Insert tab at the top.
• After that, select the Module option.

• As a result, a Module code window will appear.
• Write down the following code.

Sub GoalSeekMultipleCells()
For p = 5 To 13
Cells(p, "F").GoalSeek Goal:=0.5, ChangingCell:=Cells(p, "E")
Next p
End Sub

• Then, close the Visual Basic window.
• hen, go to the Developer tab on the ribbon.
• Select the Macros option from the Code group.

• Then, the Macro dialog box will appear.
• Select GoalSeekMultipleCells from the Macro name section.
• After that, click on Run.

• In this segment, we try to change the project progress value, and the worked hours value will change using the goal seek. See the screenshot.
• This process helps you to do goal seek for multiple cells condition.

Sub GoalSeekMultipleCells()

For p = 5 To 13
Cells(p, "F").GoalSeek Goal:=0.5, ChangingCell:=Cells(p, "E")
Next p

End Sub

Keyboard Shortcut to Open Goal Seek in Excel

In a normal case, we put the mouse cursor on the output value which we want to change. Then, we go to the Data tab on the ribbon. After that, select What-If Analysis drop-down option from the Forecast group. Then, select Goal Seek from the What-If Analysis drop-down option. But you can open the goal seek in Excel using the keyboard shortcut. In this case, put the mouse cursor on the output value which we want to change. Then press Alt+A+W+G. As a result, the goal seek dialog box will appear.

What to Do If Goal Seek Does Not Work in Excel?

The goal seek feature can fail to deliver the required result for many different cases. In this section, we will try to show the possible reason for this. Follow it carefully.

• 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.
• Another problem can arise due to iteration problems. The goal seek 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.

Conclusion

To understand the goal seek analysis in Excel, we have shown three different examples through which you can do the job. We discussed the possible advantages and how to automate the goal seek in Excel. We have shown the possible keyboard shortcut for opening goal seek and also given the possible reason why the goal seek can’t work in certain conditions. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.