Page 70 - Numerical Analysis Using MATLAB and Excel

Approximations with Spreadsheets

x = 1.27647 , y = f x() = 0.00002 .

4. We repeat the above steps for the next root near x = 2.20 , and we verify that for
x = 2.22515 , y = f x() = 0.00020 .

Another method of using the Goal Seek feature, is with a chart such as those we’ve created for the
last three examples. We will illustrate the procedure with the chart of Example 2.5.
1. We point the mouse at the curve where it intersects the x−axis, near the x = 1.30 point. A
square box appears and displays Series 1, (1.30, −0.041). We observe that other points are also
displayed as the mouse is moved at different points near the curve.
2. We click anywhere near the curve, and we observe that five handles (black square boxes) are

displayed along different points on the curve. Next, we click on the handle near the x = 1.30
point, and when the cross symbol appears, we drag it towards the x−axis to change its value.
The Goal Seek dialog box then appears where the Set cell shows B24. Then, in the To value box
we enter 0, in the By changing cell we enter A24 and we click on OK. We observe now that A24
displays 1.28 and B24 displays 0.000.

For repetitive tasks, such as finding the roots of polynomials, it is prudent to construct a template
(model spreadsheet) with the appropriate formulas and then enter the coefficients of the polyno-
*
mial to find its real roots . This is illustrated with the next example.

Example 2.6
Construct a template (model spreadsheet), with Excel, which uses Newton’s method to approxi-
mate a real root of any polynomial with real coefficients up to the seventh power; then, use it to
compute a root of the polynomial

5
4
7
6
2
3
y = fx() = x – 6x + 5x – 4x + 3x – 2x + x – 15 (2.17)
given that one real root lies in the 4 ≤≤ 6 interval.
x
Solution:
1. We begin with a blank spreadsheet and we make the entries shown in Figure 2.9.

* There exists a numerical procedure, known as Bairstow’s method, that we can use to find the complex roots of a polyno-
mial with real coefficients. We will not discuss this method here; it can be found in advanced numerical analysis textbooks.

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