Page 77 - Numerical Analysis Using MATLAB and Excel

Chapter 2 Root Approximations

Next, we will use an Excel spreadsheet to construct a template that approximates a real root of a
function with the bisection method. This requires repeated use of the IF function which has the
following syntax.
=IF(logical_test,value_if_true,value_if_false)

where

logical_test: any value or expression that can be evaluated to true or false.
value_if_true: the value that is returned if logical_test is true.

If logical_test is true and value_if_true is omitted, true is returned. Value_if_true can be another
formula.

value_if_false is the value that is returned if logical_test is false. If logical_test is false and
value_if_false is omitted, false is returned. Value_if_false can be another formula.

These statements may be clarified with the following examples.
=IF(C11>=1500,A15, B15):If the value in C11 is greater than or equal to 1500, use the value in
A15; otherwise use the value in B15.
=IF(D22<E22, 800, 1200):If the value in D22 is less than the value of E22, assign the number
800; otherwise assign the number 1200.

=IF(M8<>N17, K7*12, L8/24):If the value in M8 is not equal to the value in N17, use the value in
K7 multiplied by 12; otherwise use the value in L8 divided by 24.

Example 2.8

Use the bisection method with an Excel spreadsheet to approximate the value of 5 within
0.00001 accuracy.

Solution:

2
Finding the square root of 5 is equivalent to finding the roots of x – 5 = 0 . We expect the posi-
tive root to be in the 2 << 3 interval so we assign x = 2 and x = 3 . The average of these
x
1
2
values is x m = 2.5 . We will create a template as we did in Example 2.6 so we can use it with any
polynomial equation. We start with a blank spreadsheet and we make the entries in rows 1
through 12 as shown in Figure 2.13.

Now, we make the following entries in rows 13 and 14.

A13: 2
B13: 3
C13: =(A13+B13)/2

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