Automated Goal Seek

Automated Goal Seek Featured Image

I recently attended an online meetup where Danielle Stein Fairhurst shared a technique involving Excel’s Goal Seek feature operated by a macro.  This got my mind buzzing with ideas, so I wanted to share it with you, along with a few of my own developments.  I’ve not used the technique extensively to date, but it seems to be a useful approach.

While probably one of the best features in Excel, Goal Seek is quite clunky to use.  It’s hidden away in the Data menu; so you need to know it’s there to find it.  Wouldn’t it be great if we could make it work with a single click, or maybe automate it completely?  Well… guess what, that’s exactly what we’re going to do.

Watch the video:

Watch the video on YouTube

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0022 Automated Goal Seek.zip

The Scenario

The Goal Seek feature determines the input required to achieve a specific outcome.  The scenario we’re using is a simple business case.  Based on the sales units, sales price, cost price and fixed costs, we calculate a profit.

Business Case Scenario

The grey cells are the variables, if any of the variables change, the final profit changes.

Manual Goal Seek

First, let’s look at a manual Goal Seek.

Using the numbers above, let’s assume we want to find out how many units we would need to sell to break-even.

  1. Click Data – > What If Analysis -> Goal Seek… to open the Goal Seek tool
    Data - What If - Goal Seek
  2. In the Goal Seek window, set the following parameters:
    Set Goal Seek to Break Even

    • Set cell: E12
    • To value: 0
    • By changing cell: C4
  3. Click OK to run the Goal Seek.  Excel will now try to identify a possible solution and update cell E12 accordingly.
    Result of Goal Seek
  4. Click OK again to close the Goal Seek dialog box.

If you’re following along with the example file, you will see that we need to sell 571 units to break-even.

If one of the variables were to change (e.g., if the sales price increased to 110), then we would need to rerun the same Goal Seek process.  It’s quite a time-consuming process, plus if you are designing the spreadsheet for somebody else, then it’s not great user experience.

Let’s move on to look at a more dynamic solution.

One-click Goal Seek

Next, we will create a one-click solution using a macro and attached to a button.  Then, anytime the variables change, it will just take one click to re-run the Goal Seek.

Record the macro

In this scenario, the Macro Recorder will be a solid starting point.

  1. Click Developer -> Record Macro
    Developer Record Macro
  2. In the Record Macro dialog, accept the default option by clicking OK.
    Record Macro Window - Click OK
  3. Undertake the same steps as the manual version we used above.
  4. Click Developer -> Stop Recording
    Developer Stop Macro

Looking at the recorded code

Let’s take a brief look at the recorded code.  Click Developer -> Visual Basic, to open the Visual Basic Editor, then look at the code in Module 1.

VBE Editor with Recorded Code

The recorded code should look like the following:

Sub Macro1()
'
' Macro1 Macro
'

'

    Range("E12").GoalSeek Goal:=0, ChangingCell:=Range("C4")
End Sub

It’s just a single line of code, which we can easily edit if needed.  We will come back to the Visual Basic Editor in a few moments, but for now, it’s OK to close it.

Assign the macro to a button

The final step in this is to create a button to run the macro.

  1. Click Insert -> Shapes -> Rectangle Rounded Corners (or any other shape you like).
    Insert Rounded Corners
  2. Click on the worksheet to create the shape.
  3. Right-click the shape and select Assign Macro… from the menu.
    Assign Macro - to Shape
  4. In the Assign Macro dialog box, select the name of the macro you recorded earlier, then click OK.
    Assign Macro Window

Clicking the shape will now run the Goal Seek macro.  Format the shape however you like.

Hopefully, you see that this is a much better user experience than using the default tool.

Using Named Ranges

It can be dangerous to just use cell references in a macro.  If a user inserts a row or a column, the reference is then pointing to the wrong cell.  To make this more robust, we should create some named ranges.

Select each grey cell (i.e. those containing variables), then type a name into the name box and press return.

Create Named Range

I’ve named the cells as follows:

  • C4: SalesUnits
  • D4: SalesPrice
  • D6: VariableCostPrice
  • E10: FixedCost
  • E12: Profit
  • I6: Target Value

Named Ranges Added

Let’s update our macro for the named ranges.

Sub Macro1()

Range("Profit").GoalSeek Goal:=Range("TargetValue"), _
    ChangingCell:=Range("SalesPrice")

End Sub

By adding the target value, we can now Goal Seek to any value we want.  For example:

  • To calculate the break-even point, we enter zero as the target value, then click the button to run the macro.
  • To calculate the units required to make $50k profit, enter 50,000 as the target value, then run the macro.

Pretty good, eh?

Adding an error check

Normally, with a Goal Seek, we receive a message when a value cannot be found.

Goal Seek result not found

This does not happen with the macro, so we need to create our own error checks.  There are lots of things we could do here, but for simplicity, I have just added the following in cell F12:

=IF(Profit<>TargetValue,"<< Automated Goal Seek - no result found","")

It shows when the Goal Seek does not equal zero.

Goal Seek - with error message displayed

Depending on the accuracy you need, you may need to round the numbers.

Generate accurate VBA code in seconds with AutoMacro

AutoMacroExample

AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.

Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.

Automated Goal Seek

Now for the next level stuff.

Add two more named ranges:

SetCell and ChangeCell Named Ranges

  • I4: SetCell
  • I8: ChangeCell

Enter the following values into those cells; you’ll soon see why.

  • I4 = Profit
  • I8 = SalesUnits

Next, add a new macro into the worksheet module, using the worksheet change event.

Macro included in the worksheet module

The comments provide information about each line of code.  The macro is:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim inputCells As Range

'List all the input cells.
Set inputCells = Range("SalesUnits, SalesPrice, VariableCostPrice, FixedCost, " & _
    "TargetValue, SetCell, ChangeCell")

'Run the macro if an input cell changes
If Not Application.Intersect(Range(Target.Address), inputCells) Is Nothing Then

    'Run the Goal Seek using the values in the SetCell, TargetValue 
    'and ChangeCell named ranges
    Range(Range("SetCell").Value).GoalSeek Goal:=Range("TargetValue").Value, _
        ChangingCell:=Range(Range("ChangeCell").Value)

End If

End Sub

Now every-time we change an input cell, the Goal Seek fires automatically.

Auto Goal Seek

The Set Cell and Change Cell must both be the names of named ranges

If we want the Goal Seek to change a different variable, we adjust the Change Cell.

Pretty cool, eh?  Now we don’t even need to click a button 🙂

Conclusion

In this post, we’ve learned how to control Goal Seek using a macro.  There are lots of Excel features that can be operated by a few lines of code.  Why not go and explore and see what else you can find.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

 

Leave a Reply

Your email address will not be published. Required fields are marked *