Page 203 - Excel for Scientists and Engineers: Numerical Methods

180 EXCEL: NUMERICAL METHODS

and the two cells will display zero values. Now choose Options ... from the
Tools menu and choose the Calculation tab. Check the Iteration box and press
OK. Figure 8-39 shows the final values in the table, after circular reference
iteration is complete.

Figure 8-39. Calculating two intersections of a line and a curve
by the Newton-Raphson method (after creating intentional circular references).
(folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference (2)')

A 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 XI As Double, Y1 As Double, X2 As Double, Y2 As Double
Dim m As Double

If IsMissing(initia1-value) Then initial-value = changing-cell
If initial-value = I"' Then initial-value = changing-cell

tolerance = 0.0000000001
incr = 0.00000001
XRef = changing-celI.Address
Formulastring = target-cell.Formula
Formulastring = Application.ConvertFormula(FormulaString, xlAl , xlAl , -
xl Absolute)