Page 180 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 8 ROOTS OF EQUATIONS 157

of the function y become zero, or at least very close to zero. The computer code
that performs the Goal Seek function probably involves the Newton-Raphson
method.;
As an example to illustrate the use of Goal Seek ..., we'll return to the cubic
equation 8-1,y = x3 + 0.13~~ - 0.0005~ - 0.0009. Figure 8-12 shows a part of the
data table that was used to produce the chart shown in Figure 8-1.

Figure 8-12. Part of a data table.
(folder 'Chapter 08 Examples', workbook 'Roots of Equations', worksheet 'Using Goal Seek')

It can be seen that one of the roots of this function must lie between x = -0.13
and x = -0.12, since there is a change in sign of the function somewhere in this
interval. To use Goal Seek.. ., enter a trial value of x in a cell and the function in
another cell, as illustrated in Figure 8-13. The cell containing the value of x is
referred to as the changing cell, the cell containing the function as the target cell
or the objective.

Figure 8-13. Target Cell and Changing Cell for Goal Seek.
(folder 'Chapter 08 Examples', workbook 'Roots of Equations', worksheet 'Using Goal Seek')

Now choose Goal Seek ... from the Tools menu to display the Goal Seek
dialog box (Figure 8-14). (Although not necessary, it's convenient to select the
target cell before beginning.)

* According to Microsoft, "Goal Seek uses an iterative process in which the source cell is
incremented or decremented at varying rates until the target value is reached."