How to use Goal seek in Excel | Steps for Goal seek in Excel | VBA codes for goal seek

November 21, 2024May 6, 2024 by Insightoriel

Many a times as data analyst, we are doing scenario planning or simulation. Let’s say (simple example) we have a target of revenue of 200, fixed unit price of 5 and need to tell how many units need to be sold to achieve the targeted revenue. You must be thinking that it can be achieved by doing 200/5. But I’m using this as an example to start off and later will discuss more complex example which will difficult to manage with simple arithmetic formulas. Excel ha ms inbuilt goal seek function for this. Let’s discuss goal seek in excel.

Table of Contents

Toggle

How to use goal seek in excel

Goal Seek in Excel is a powerful tool that helps you find the optimal value for a variable that satisfies a specific condition. Here’s how to perform Goal Seek.

Method 1: Using the Goal Seek Dialog Box

1. Select the cell containing the formula you want to adjust.
2. Go to the “Data” tab in the ribbon.
3. Click on “What-If Analysis” in the “Data Tools” group.
4. Select “Goal Seek” from the drop-down menu.
5. In the Goal Seek dialog box:
   • Set cell: Select the cell containing the formula.
   • To value: Enter the desired result.
   • By changing cell: Select the cell you want to adjust.
6. Click “OK” to run Goal Seek.

Method 2: Using the Shortcut

Select the cell containing the formula.

1. Press Alt+A+W+G (Windows) or Command+Option+G (Mac).

An Example

Suppose you want to determine the interest rate required to achieve a monthly payment of $500 on a $20,000 loan with a 5-year term.

1. Set up the formula: =PMT(interest_rate, 60, 20000) in cell A1.
2. Goal Seek:
   • Set cell: A1
   • To value: 500
   • By changing cell: interest_rate (cell B1)
3. Run Goal Seek.

Excel will adjust the interest rate in cell B1 to achieve the desired monthly payment of $500.

Few Tips and Variations

1. Multiple variables: Goal Seek can adjust multiple variables simultaneously.
2. Constraints: Use the “Solver” add-in for more complex optimization problems with constraints.
3. Non-linear problems: Goal Seek may not work for non-linear problems; use the “Solver” add-in instead.
4. Multiple solutions: Goal Seek may find multiple solutions; use the “Solver” add-in to explore alternative solutions.

Common Errors

1. #NUM! error: Check for division by zero or invalid input.
2. “No solution” error: Check for inconsistent or impossible goals.

There are few Alternative Tools that can be explored to solve such problems.

1. Solver add-in (more advanced optimization capabilities)
2. Scenario Manager (for exploring multiple scenarios)
3. Sensitivity analysis (for analyzing how changes affect outcomes).

VBA macro code for Goal Seek in Excel

Simple Goal Seek:

MacroSub GoalSeekSimple() ' Declare variables Dim targetCell As Range Dim changingCell As Range Dim targetValue As Double ' Set target cell, changing cell, and target value Set targetCell = Range("A1") ' Cell containing formula Set changingCell = Range("B1") ' Cell to adjust targetValue = 500 ' Desired result ' Goal Seek Range(targetCell).GoalSeek GoalValue:=targetValue, ChangingCell:=changingCell End Sub

Advanced Goal Seek Macro with Error Handling:

Sub GoalSeekAdvanced() ' Declare variables Dim targetCell As Range Dim changingCell As Range Dim targetValue As Double Dim iterations As Long Dim tolerance As Double ' Set target cell, changing cell, and target value Set targetCell = Range("A1") ' Cell containing formula Set changingCell = Range("B1") ' Cell to adjust targetValue = 500 ' Desired result ' Set iterations and tolerance (optional) iterations = 100 tolerance = 0.01 ' Goal Seek with error handling On Error Resume Next Range(targetCell).GoalSeek GoalValue:=targetValue, ChangingCell:=changingCell, _ MaxIterations:=iterations, Tolerance:=tolerance If Err.Number <> 0 Then MsgBox "Goal Seek failed: " & Err.Description End If On Error GoTo 0 End Sub

How to Use:

1. Open Excel and press Alt + F11 to open VBA Editor.
2. Insert a new module (Insert > Module).
3. Paste the code into the module.
4. Update the target cell, changing cell, and target value variables.
5. Run the macro (F5 or Run > Run Sub/UserForm).

Tips and Variations:

1. Use Range or Cells to reference cells.
2. Adjust iterations and tolerance for better convergence.
3. Use Do...Loop for iterative Goal Seek.
4. Integrate with other VBA macros or Excel formulas.
5. Error handling: On Error Resume Next, Err.Number, Err.Description.

All the best.

Please follow and like us: