Page 173 - Excel for Scientists and Engineers: Numerical Methods

150 EXCEL: NUMERICAL METHODS

interval between them to get a further improvement. Repeat the process until a
desired level of accuracy is attained. Figure 8-5 illustrates the application of this
method, using equation 8-2. Only a portion of the table is shown; 34 rows were
required to reach convergence at the 1E-10 level, at which point x =
1.96445854473859.

Figure 8-5. Using the binary search method to find a real root of a function.
(folder 'Chapter 08 Examples', workbook 'Roots of Equations', worksheet 'Binary Search Method')

To construct the worksheet of Figure 8-5, the initial values xl and x2 were
entered in cells A3 and C3, respectively, and the formula for the function in cells
83 and D3. Next, the formulas that perform the binary search were entered in
row 4; the formula in cell A4 calculates the midpoint value between the x values
in the previous row
=( C3+A3)/2
and the formula in cell C4 selects the y value that has the opposite sign to the
value in the previous row.
=IF(SlGN(B4)<X3lGN(B3),A3,C3),
Cells 84 and D4 contain the formula for the function. Finally, the formulas
in A4:D4 were filled down into subsequent rows. Each row constitutes an
iteration cycle; convergence was observed visually.
Although unsophisticated, this method will always find a root.