Goal seek is a tool in Microsoft Excel that allows you to find the value of a variable in a calculation at your predetermined result. The calculation can comprise one formula or a series of formula.
Formula: y = 3 + x
Problem: find x when y = 10
To solve this problem, open an Excel worksheet.
1. Enter zero (0) in cell A1 as the x variable
2. Enter formula in cell B1 : =3+A1
3. Click “Data” to show Data tab
4. Click “What-if Analysis” to show the sub menus
5. Select “Goal Seek…”
6. You will be shown an input dialog. Input the dialog as shown below and click “OK”. You can either click the desired cell for “Set cell” and “By changing cell” input or directly writes the address.
7. The result is shown in figure below. A dialog box appear telling you that the goal seek process has been successful with the current value is 10 compare to target value. Cell A1 will contain “7” which is the appropriate value for x.
In some cases, the calculation is more complicated, involving numerous formulas with some variables which shall be determined using this analysis method. Thus, it would be better to include the analysis in VBA. With this way, you can perform the analysis with just one click on a button.
Using the same problem in Example 1, we are trying to write the VBA code to use the Goal Seek Analysis.
1. The goal seek analysis requires two (2) values to be predetermined, i.e. the initial value for input (x) and the target value for result (y). You may want to put these values into cell C1 and C2 respectively. Put 0 as the initial value in cell C1 and 10 as the target value in cell C2.
Note: in non-linear formulas, different initial value will give you different result !
2. Click “Developer” to show Developer tab
3. Click “Insert” to show sub menus. We want to create a button
4. Click the button icon in “From Controls”, and create the button in your worksheet. Once the button is created, a dialog shows up. The button shall be assigned to a macro for a click action. This means, the assigned macro will be executed whenever the button is clicked. Click the “New” button to write the code.
5. Write down the code as shown below.
‘Setting initial value
Range(“A1”).Value = Range(“C1”).Value
‘Performing goal seek analysis
Range(“A2”).GoalSeek Goal:=Range(“C”).Value, ChangingCell:=Range(“A1”)
6. Done. Now, close the visual basic and back to your worksheet. Try clicking the button and see what happened.