Page 78 - Numerical Analysis Using MATLAB and Excel

The Bisection Method for Root Approximation

A B C D E F G H
1 Spreadsheet for finding approximations of the real roots
2 of polynomials using the Bisection method
3
2
4 Equation: y = f(x) = x − 5 = 0
5
6 Powers of x and corresponding coefficients of given polynomial f(x)
7 Enter coefficients of f(x) in Row 9
8 x 7 x 6 x 5 x 4 x 3 x 2 x Constant
9 0.00000 0.00000 0.00000 0.00000 0.00000 1.00000 0 -5
10
11 x 1 x 2 x m f(x 1 ) f(x m ) f(x 1 )f(x m )
12 (x 1 +x 2 )/2
Figure 2.13. Partial spreadsheet for Example 2.8

D13: =$A$9*A13^7+$B$9*A13^6+$C$9*A13^5+$D$9*A13^4
+$E$9*A13^3+$F$9*A13^2+$G$9*A13^1+$H$9*A13^0
E13: =$A$9*C13^7+$B$9*C13^6+$C$9*C13^5+$D$9*C13^4
+$E$9*C13^3+$F$9*C13^2+$G$9*C13^1+$H$9*C13^0
F13: =D13*E13
A14: =IF(A14=A13, C13, B13)
B14: =IF(A14=A13, C13, B13)

We copy C13 into C14 and we verify that C14: =(A14+B14)/2
Next, we highlight D13:F13 and on the Edit menu we click on Copy. We place the cursor on D14
and from the Edit menu we click on Paste. We verify that the numbers on D14:F14 are as shown
on the spreadsheet of Figure 2.14. Finally, we highlight A14:F14, from the Edit menu we click on
Copy, we place the cursor on A15, and holding the mouse left button, we highlight the range
A15:A30. Then, from the Edit menu, we click on Paste and we observe the values in A15:F30.

The square root of 5 accurate to six decimal places is shown on C30 in the spreadsheet of Figure
2.14.

Numerical Analysis Using MATLAB® and Excel®, Third Edition 2−25
Copyright © Orchard Publications