Goal Seek Custom Function

The Newton-Raphson custom function described in a previous section was modified to create a custom function that performs goal seeking. This custom function can be used in the same way as Excel's built-in Goal Seek tool — to find the value of x (the changing cell) that makes the function y (the target cell) have a specified value. The VBA code is shown in Figure 8-40.

Option Explicit

Function GoalSeek(target_cell, changing_cell, objective_value, Optional _ initial_value) As Double

'Finds value of X to make Y have a desired value 'This is a modified version of NewtRaph

Dim tolerance As Double, incr As Double Dim XRef As String, FormulaString As String Dim I As Integer

Dim X1 As Double, Y1 As Double, X2 As Double, Y2 As Double Dim m As Double

If lsMissing(initial_value) Then initial_value = changing_cell If initial_value = "" Then initial_value = changing_cell tolerance = 0.0000000001 incr = 0.00000001

XRef = changing_cell.Address FormulaString = target_cell.Formula

FormulaString = Application.ConvertFormula(FormulaString, xlA1, xlA1, xIAbsolute)

X1 = initial_value Fori = 1 To 100

Y1 = Evaluate(Application.Substitute(FormulaString, XRef, X1)) If X1 = 0 Then X1 = incr X2 = X1 + X1 * incr

Y2 = Evaluate(Application.Substitute(FormulaString, XRef, X2))

'Exit here if a root is found

If Abs((X2 - X1) / X2) < tolerance Then GoalSeek = X2. Exit Function X1 =X2 Next I

'Exit here with error value if no root found GoalSeek = CVErr(xlErrNA): Exit Function End Function

End Sub_

Figure 8-40. VBA code for the GoalSeek custom function, (folder 'Chapter 08 Examples', workbook 'GoalSeek Fn', module 'Module 1')

This custom function can be used in the same way as Excel's built-in Goal Seek... tool to find the value of x (the changing cell) that makes the function y (the target cell) have a specified value. The syntax of the function is

GoalSeek(targef_ce//, changing_cell, objective_value, initial_value) The argument target_cell is a reference to a cell containing a formula F(x). The argument changing_cell is a cell reference corresponding to x, the independent variable. (The formula in target_cell must depend on changing_cell.) These two arguments correspond exactly to the Goal Seek tool's inputs Set Cell and By Changing Cell. The argument objective_value (Goal Seek's To Value input) is the value you want target_cell to attain. The optional argument initial_value is used, in cases where more that one value of x can result in the function F(x) having the desired value, to control the value of x that is returned.

Note that when using the Goal Seek tool, To Value can only be a fixed value, not a cell reference, whereas when using the GoalSeek custom function, the argument can be a cell reference. Thus, when objective_value is changed, the GoalSeek return value updates automatically.

As an illustration, we will use the GoalSeek custom function to find the value of x that makes the function y = x2 + 6x -10 have a specified value, namely y = 210. In the spreadsheet shown in Figure 8-41 the table in $A$5:$B20 provides the x, y values of the function that are plotted in Figure 8-42. The adjustable parameters of the function are in $E$5:$E$7. The adjustable value of the intersection point H is in cell E10. Cell D14 contains the formula

=goalseek(B5,A5,E10)

Note that the GoalSeek function does not modify the value of the changing cell (in this example cell A5) nor does it result in a change in the cell containing the function (in this example cell B5). These values are merely copied and used as inputs for the VBA code. The final value of the changing cell is returned by the GoalSeek function (in this example in cell D14). As a check, the target cell formula was entered in cell E14 so as to calculate F{x) using the value of x returned by GoalSeek.

Some functions have more than one value of x that can satisfy the relationship F(x) = objective_value; in these cases the user must use the optional argument initial_value to control the value of x that is returned.

A

B

C

D E

1

Intersecting Lines in a Chart

(Using GoalSeek custom function to find the intersection

2

of curve y and horizontal H)

3

y ■ aa-x* + bb-x + cc

4

X

y

Parameters of y

5

o

-10

aa 1

6

1

-3

bb 6

7

2

6

CC -10

8

3

17

"" T ~~ i

9

4

30

Value of H

ür

5

45

210

11

6

62

"I j

12

7

81

Using GoalSeek Fn

13

8

102

x y

14

9

125

12.1327 210

15

10

150

ZI____L."

16

11

177

17

12

206

18

13

237

19

14

270

20

15

305

Figure 8-41. Using the GoalSeek custom function to find the value of x that makes the function y = x2 + 6x - 10 have a specified value (here, y = 210). (folder 'Chapter 08 Examples', workbook 'GoalSeek Fn', sheet 'Intersection of line with h (2)')

Figure 8-41. Using the GoalSeek custom function to find the value of x that makes the function y = x2 + 6x - 10 have a specified value (here, y = 210). (folder 'Chapter 08 Examples', workbook 'GoalSeek Fn', sheet 'Intersection of line with h (2)')

If you change the values of aa, bb, cc, or H, the function value will update to find the new intersection value. In contrast, if you use the Goal Seek... tool, you must repeat the action of goal-seeking each time you change any of the parameters.

A limitation of the GoalSeek custom function is that target_ceil must contain the complete expression dependent on changing_cell. Only the instances of changing_cell that appear in the formula in target_cell will be used in the Newton-Raphson calculation.

Table Values For
Figure 8-42. The value of x that makes the function y = x2 + 6x- 10 have the value 210. (folder 'Chapter 08 Examples', workbook 'GoalSeek Fn', sheet 'Intersection of line with h (2)')

The CD contains an example of the use of the GoalSeek function to find approximately 180 intersection points of lines with a curve in a chart (see folder 'Chapter 08 Examples', workbook 'Diatomic Molecule', sheet 'Vibrational Energy Levels'). The resulting chart is shown in Figure 8-43. The chart contains two data series. The first data series shows the continuous function of energy as a function of distance r. The second data series shows the approximately 90 horizontal vibrational energy levels.

Vba Custom Function Show

Figure 8-43. Using the GoalSeek custom function to find multiple intersections of lines in a chart, (folder 'Chapter 08 Examples', workbook 'Diatomic Molecule', sheet 'Sheetl')

Internuclear distance, A

Figure 8-43. Using the GoalSeek custom function to find multiple intersections of lines in a chart, (folder 'Chapter 08 Examples', workbook 'Diatomic Molecule', sheet 'Sheetl')

Was this article helpful?

0 -2

Post a comment