Page 182 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 8 ROOTS OF EQUATIONS 159

Figure 8- 17). The Maximum Change parameter sets the convergence limit; when
the value of the target cell becomes less than this value, iteration ceases. The
default value for Maximum Change is 0.001, which is suitable for this problem,
but will not be suitable for many other problems. For a problem where the
magnitude of the result (the changing cell value) is a very small number, you can
set Maximum Change to a value such as 1E-15. Alternatively, you can set it to
zero, which will usually result in Goal Seek completing 100 iteration cycles
before quitting.

Figure 8-17. The Calculation Options dialog box.

Since Goal Seek ... almost certainly uses something like the Newton-
Raphson method to find a root, it should be clear from Figure 8-1 that the trial
value that you use will determine the root that is found. The cubic equation that
we used in our example, shown in Figure 8-1, has three real roots. It is clear that
if 0.01 is used as initial estimate, the largest of the three roots will be calculated,
while using -0.2 as an initial estimate will result in the smallest of the three roots.
Thus, to obtain a particular root, some guidance must be provided by the user.