Use Goal Seek

Use Goal Seek

Goal Seek can be super helpful for you to solve complex problems. Learn more about goal seek from here before you use this code.

Sub GoalSeekVBA()

Dim Target As Long

On Error GoTo Errorhandler

Target = InputBox("Enter the required value", "Enter Value") Worksheets("Goal_Seek").Activate

With ActiveSheet .Range("C7")

.GoalSeek_ Goal:=Target, _

ChangingCell:=Range("C2")

End With

Exit Sub

Errorhandler: MsgBox("Sorry, value is not valid.")

End Sub

With VBA

How to Open Goal Seek

To open goal seek use one of these two methods.

• Go to Data Tab → What If Analysis → Goal Seek.

• Use the shortcut key Alt + T + G.

You can use these methods to activate goal seek in Microsoft Excel 2007 to 2016 versions.

Components of Goal Seek Function

When you open goal seek, you’ll get a small pop-up to input data. Basically, it has three required components.

1. Set Cell: Cell in which you want the desired result. Make sure, the cell you are referring here has a formula in it.
2. To Value: Value you want as a result.
3. By Changing Cell: Cell in which you want alteration to come up with the result equals to the “To Value”. Make sure, the cell you are referring here is used in the formula in the cell which is referred in “Set Cell”.

Solving a Problem in Excel with Goal Seek

Imagine you’re working in a company who is trying to get a tender order through quoting the lowest price.
For this, you need to prepare a price structure in which your landing price (to the customer) should be $1000.
Whereas your present final price is $1089.9.

If you want to do it manually you have two options.

1. Perform a back calculation where you have to deduct all the taxes and charges from the landing price ($1000) to come to the base price.
2. Or, you have to tweak in your base price to get to the desired final price.

It’s not as easy as it sounds but goal seek can be a game changer here.