-1.1415 + 0.8212i -1.1415 - 0.8212i 0.6113 + 0.9476i 0.6113 - 0.9476i 1.0604 Example 2.5
Compute the real roots of the trigonometric function
(2.16) using Excel.
Solution:
We invoke (open) the spreadsheet of one of the last two examples, that is, poly01.xls or poly02.xls, and save it with another name, such as poly03.xls.
Since we do not know where real roots (if any) are in the x−axis interval, we arbitrarily choose the interval . Then, we enter −1.00 and −0.90 in A2 and A3 respectively, Using the Auto- Fill feature, we fill−in the range A4:A72 and thus we have the interval from −1 to 6 in increments
of 0.10. Next, we select B2 and we enter the formula for the given equation, i.e., =COS(2*A2)+SIN(2*A2)+A2−1
and we copy this formula to B3:B62.
There is a root at ; this is found by substitution of zero into the given equation. We observe that Columns A and B contain the following sign changes (only a part of the table is shown):
We observe two sign changes. Therefore, we expect two more real roots, one in the interval and the other in the interval. If we redefine the
range as 1 to 2.5, we will find that the other two roots are approximately and .
Approximate values of these roots can also be observed on the plot of Figure 2.8 where the curve crosses the . y = f x( ) = cos2x+sin2x x+ –1 1 x 6≤ ≤ – x = 0 x f(x) 1.20 0.138 1.30 -0.041 2.20 -0.059 2.30 0.194 Sign Change Sign Change 1.20 x 1.30≤ ≤ 2.20 x 2.30≤ ≤ x axis– x = 1.30 x = 2.24 x axis–
Chapter 2 Root Approximations
Figure 2.8. Graph for Example 2.5
We can obtain more accurate approximations using Excel’s Goal Seek feature. We use Goal Seek when we know the desired result of a single formula, but we do not know the input value which satisfies that result. Thus, if we have the function , we can use Goal Seek to set the dependent variable to the desired value (goal) and from it, find the value of the independent variable which satisfies that goal. In the last three examples our goal was to find the values of
for which .
To illustrate the Goal Seek feature, we will use it to find better approximations for the non−zero roots of Example 2.5. We do this with the following steps:
1. We copy range A24:B24 (or A25:B25) to two blank cells, say J1 and K1, so that J1 contains
1.20 and K1 contains 0.138 (or 1.30 and −0.041 if range A25:B25 was copied). We increase the accuracy of Columns J and K to 5 decimal places by clicking on Format, Cells, Numbers tab. 2. From the Tools drop menu, we click on Goal Seek, and when the Goal Seek dialog box appears,
we make the following entries: Set cell: K1
To value: 0
By changing cell: J1
3. When this is done properly, we will observe the changes in J1 and K1. These indicate that for x f(x) -1.00 -3.325 -0.90 -3.101 -0.80 -2.829 -0.70 -2.515 -0.60 -2.170 -0.50 -1.801 -0.40 -1.421 -0.30 -1.039 -0.20 -0.668 -0.10 -0.319 0.00 0.000 0.10 0.279 0.20 0.510 0.30 0.690 x f(x) 0.40 0.814 0.00 0.000 0.50 0.882 1.20 0.138 0.60 0.894 1.30 -0.041 0.70 0.855 2.20 -0.059 0.80 0.770 2.30 0.194 0.90 0.647 f(x) = cos2x + sin2x + x - 1 -4 -2 0 2 4 6 -1 0 1 2 3 4 5 6 x f( x)
Real Root between Real Root between Real Root at
y = f x( ) y
x x
Approximations with Spreadsheets
, .
4. We repeat the above steps for the next root near , and we verify that for
, .
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 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
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
(2.17) given that one real root lies in the interval.
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.
x = 1.27647 y = f x( ) = 0.00002 x = 2.20 x = 2.22515 y = f x( ) = 0.00020 x = 1.30 x = 1.30 y = f x( ) = x7–6x6+5x5–4x4+3x3–2x2+x 15– 4 x 6≤ ≤
Chapter 2 Root Approximations
Figure 2.9. Model spreadsheet for finding real roots of polynomials.
We save the spreadsheet of Figure 2.9 with a name, say template.xls. Then, we save it with a dif- ferent name, say Example_2_6.xls, and in B16 we type the formula
=A16-($A$7*A16^7+$B$7*A16^6+$C$7*A16^5+$D$7*A16^4 +$E$7*A16^3+$F$7*A16^2+$G$7*A16^1+$H$7)/
($B$12*A16^6+$C$12*A16^5+$D$12*A16^4+$E$12*A16^3 +$F$12*A16^2+$G$12*A16^1+$H$12)
The use of the dollar sign ($) is explained in Paragraph 4 below.
The formula in B16 of Figure 2.10, is the familiar Newton’s formula which also appears in Row 14. We observe that B16 now displays #DIV/0! (this is a warning that some value is being
divided by zero), but this will change once we enter the polynomial coefficients, and the coeffi- cients of the first derivative.
2. Since we are told that one real root is between 4 and 6, we take the average 5 and we enter it in A16. This value is our first (initial) approximation. We also enter the polynomial coefficients, and the coefficients of the first derivative in Rows 7 and 12 respectively.
3. Next, we copy B16 to C16:F16 and the spreadsheet now appears as shown in the spreadsheet of Figure 2.10. We observe that there is no change in the values of E16 and F16; therefore, we terminate the approximation steps there.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 A B C D E F G H
Spreadsheet for finding approximations of the real roots of polynomials up the 7th power by Newton's Method.
Powers of x and corresponding coefficients of given polynomial p(x)
Enter coefficients of p(x) in Row 7
x7 x6 x5 x4 x3 x2
x Constant
Coefficients of the derivative p'(x)
Enter coefficients of p'(x) in Row 12
x6 x5 x4 x3 x2
x Constant
Approximations: xn+1 = xn− p(xn)/p'(xn)