How to Use the Goal Seek Analysis in Excel (Examples, Advantages, and Disadvantages)

What Is Goal Seek in Excel?

Goal Seek is a built-in tool in Excel that performs What-If Analysis. It helps you find the specific input value needed to achieve a desired output in a formula. Essentially, it back-solves the problem by iteratively adjusting guesses until it reaches the solution.

When using Goal Seek, you’ll encounter three parameters:

1. Set Cell (Compulsory): This is the cell containing the formula whose value you want to change.
2. To Value (Compulsory): The desired target value for the output.
3. By Changing Cell (Compulsory): Goal Seek adjusts the value in this cell to achieve the desired output using the formula.

In the below overview image, we used $180,000 as the loan amount first and got a monthly payable value of $1822. Using the Goal Seek analysis, we have brought down this monthly payable amount to $1500, changing the loan amount. The final loan amount became $148,155.

Example 1 – Loan Amount Calculation

Suppose you’ve taken a $180,000 loan from a bank with an annual interest rate of 4%. You want to pay it off over a 10-year period. Using the PMT function, you find the monthly payable amount to be $1,822.41. Now, if you wish to pay $1,500 instead, we can use Goal Seek to adjust the loan amount.

Now, if you wish to pay $1,500 instead, we can use Goal Seek in cell C9 to adjust the loan amount.

Follow the steps below to do this.

• Select cell C9.
• Go to the Data tab.
• Expand the What-If Analysis tools list in the Forecast group.
• Click Goal Seek to open the dialog box.

• Set the following parameters:
  • Set cell: Choose the cell containing the formula result you want to achieve (e.g., C9).
  • To value: Enter the desired output (e.g., -1500 for a loan payment of $1,500).
  • By changing cell: Specify the cell where Goal Seek will adjust the value (e.g., $C$6 for the loan amount).

• Click OK to see the new input value that achieves the desired output.

Example 2 – Calculating Time Required for Desired Profit Return

Suppose you have a fixed deposit amount (cell C6) and want to determine the time it will take to achieve a desired profit. The return amount after 2 years is in cell C9, but you aim for a profit equal to the value in C10. Using the FV function, you can calculate the return on investment after 2 years:

To find the required time, follow these steps:

• Select cell C9.
• Go to the Data tab, expand the What-If Analysis tools, and select Goal Seek.
• In the Goal Seek dialog box:
  • Set cell: C9
  • To value: 54,636.35
  • By changing cell: $C$8
• Press OK.

The result will show the required time (approximately 3 years).

Example 3 – Minimum Number for a Fixed Grade in Final Exam

Given exam scores in cells C12:E12 (averaged from four exams), you want all students to achieve a final grade of 75.

To determine the marks needed in Exam 4 (cell C10), follow these steps:

• In the Goal Seek dialog box:
  • Set cell: C12
  • To value: 75
  • By changing cell: $C$10
• Press OK.

The required marks for Exam 4 will appear in cell C10.

Repeat these steps for other students, adjusting final marks as needed.

Automating Goal Seek with Excel Macros

We have a dataset that contains the names of some projects in the range B7:B15 and the hours required to finish those projects in the range C7:C15. We will input the project’s progress in cell G7. The code will use Goal Seek to assign the same value in the range D7:D15. The VBA code will return the hours required to finish that percent of work in the range E7:E15.

Follow the steps below.

• Click the Developer tab.
• Insert a Button (Form Control).

• Assign the name Goal Seek to the button.
• Drag the plus icon that has appeared in your worksheet and adjust it to a suitable size.
• Click on the button, click on CTRL+C to copy and CTRL+V to paste a new button.

• Assign the name Reset Progress to the second button.
• Right-click the Goal Seek button and Assign a Macro.

• Use the following VBA code to assign values to cells D7:D15 based on the progress in cell G7:

Sub Button5_Click()
Dim m As Integer
Dim k As Integer
'Assign formula to cells D7 to D15
For m = 7 To 15
    Cells(m, "D").GoalSeek Goal:=Cells(7, "G"), 
    changingCell:=Cells(m, "E")
Next m
End Sub 
			

• Assign another macro to the Reset Progress button:

Sub Button4_Click()
 'Selecting cells and clearing contents
 Range("E7:E15,G7").Select
 Selection.ClearContents
End Sub 
			

We have automated the Goal Seek analysis.

• Now, input 60% in cell G7 and click the Goal Seek button.

• The automation will adjust values in the range D7:E15.
• Use the Reset Progress button to clear the results.

View the gif below for an overview of the automation process.

How to Get More Precise Values Using Goal Seek

By default, Goal Seek provides results based on Excel’s default precision settings. However, if you need a more precise value, you can adjust the settings. Follow these steps:

Steps:

• Click File and select Options from the left pane. The Excel Options dialog box will open.

• Choose Formulas.
• Under Calculation options, change the Maximum Change value to something smaller (e.g., 0.0000001).

Now, when you use Goal Seek, you’ll get a more precise result.

Troubleshooting If Goal Seek Is Not Working

In some cases, Goal Seek may not work as expected. Here are two common reasons:

1. Wrong Cell Reference or No Formula in Set Cell

• Ensure that the cell reference you provide in the Goal Seek parameters is correct.
• Make sure the Set Cell contains a formula. Goal Seek won’t work if the cell doesn’t have a formula.

2. Circular Reference in Cell

• If there’s a circular reference involving any cell, you’re using in Goal Seek, it won’t work.
• Check for circular references in the Goal Seek parameters.

Advantages of Goal Seek Analysis in Excel

• Input Value Determination: Goal Seek helps you find the input value needed to achieve a known output. For instance, you can calculate the product price when you know the cost and desired profit.
• Sales Target Calculation: If you have a fixed product price, Goal Seek can determine the number of products to sell for a targeted profit.

Disadvantages of Goal Seek Analysis in Excel

• Potential Inaccuracy: In some scenarios (e.g., vote casting in an election), Goal Seek may not provide accurate results.
• Extreme Input Values: When the input value is significantly above or below the desired output, Goal Seek might struggle.
• Avoid Hard Coding: Instead of hard-coding values, use cell references for flexibility.

Download Practice Workbook

You can download the practice workbook from here:

Goal Seek in Excel: Knowledge Hub

• How to Use Goal Seek in Excel
• How to Automate Goal Seek in Excel
• How to Do What-If Analysis Using Goal Seek in Excel
• How to Use Goal Seek to Find an Input Value
• Excel Macro to Goal Seek for Multiple Cells

<< Go Back to What-If Analysis in Excel | Learn Excel