Practice 1
Name: Implementing Bisection Method using Excel.
1. Objective: to find the root of non-linear equation using bisection Method in Excel.
2. Equipment: Pc or Laptop whit Microsoft Excel release 2016 software program.
3. Background: Non-linear equations may be polynomials or generally non-linear
equations. A root of the equation is simply a value of the independent variable that
satisfies the equation f(x)=0. The equations obtained in real life by people such as
scientists and engineers often are not easy to solve. algebraic techniques such
as transposing terms, factorizing and using the quadratic formula are not
suitable. another method is to use numerical methods which are available to
approximate an answer or root of the equation to a certain level of accuracy.
4. Procedure: Find root of 𝑓(𝑥) = √𝑥 − cos(𝑥).using bisection It is easy to see
that estimating is equivalent to finding the positive root of the polynomial
We know, by the Intermediate Value Theorem, that since f is continuous, and since
f(0) < 0 and f(1) > 0, f must have a root between 0 and 1. Thus the root lies between
[0,1]. To find this using the Bisection method, we will want to use the IF function in
Excel. = IF(logical_test, value_if_true, value_if_false) takes < value_if_true > if the <
logical_test > is true, and takes < value_if_false > if the < logical_test > is false.
Here’s how I used this in the spreadsheet show in figure 2 below.
This method can be better understood by looking at a graph of the function show in
figure 1 below.
Figure1. graph of the function 𝒇(𝒙) = √𝒙 − 𝐜𝐨𝐬(𝒙)
Figure 2. Spreadsheet of Excel with implementation of bisection method
Where:
Iteration = Number of actual iteration F(xa) evaluate function with (xa) value
Xa= initial value of lower interval F(xb) evaluate function wiht (xb) value
Xb= final value of Upper interval F(xr) evaluate function wiht (xr) value
xr = midpoint between xa and xb i.e xa + xb /2 F(xa)*f(xr) multiply values of f(xa) *f(xr)
Error = abs((xr2-xr1)/xr2)*100 Ending =si(error<1,then “finish” else “continued”
The value xr is the midpoint between xa and xb, so xr=promedio(xa+xb)/2.
The two function values f(xa) and f(xr) are just =raiz(0)-cos(0), and raiz(0.5)-
cos(0.5).
The tricky parts starting in cell A20 and B20 where xa or xb must have changed by
xr. There, I used IF statements that looked kind of like this: IF(f(xa)*f(xr)>=0, xr,xa)
and IF(f(xa)*f(xr)<=0, xr, xb), and exactly like this: =IF(G19>=0,D19,B19) and
=IF(G19<=0,D19,C19). though you might use different cell references.
This works because if f(xr)*f(xa) is positive, then you want to keep the old xb and
replace the old xa with xr. On the other hand, if f(xr)*f(xa) is negative, then you
replace the old xb with xr, and keep the old xa. (In the unlikely event that f(xr) = 0,
this particular spreadsheet malfunctions slightly.
5. Discussion
1. by using Excel spreadsheet software program, find the root of √80 use Bisection
method.
2. Use excel spreadsheet software program to find the roots of the following equation
using bisection method:
a. x2 − 11x + 10 = 0 b. f(x) = 2ex − 2x – 3 c. 3x3 + 3x2 − 3x – 1= 0
Homework complete the rest of procedure
Calling Sequence
Solution by Maple release 18
Numerically approximate the real roots of an expression using the bisection method
Library to use Student[NumericalAnalysis]
Bisection(f, x=[a, b], opts)
Bisection(f, [a, b], opts)
Parameters
f = algebraic; expression in the variable x representing a continuous function
x = name; the independent variable of f
a= numeric; one of two initial approximates to the root
b= numeric; the other of the two initial approximates to the root
Examples
restart;
with(Student[NumericalAnalysis]): # library that contained the function
F=x^3−7*x^2+14*x−6:
plot(F, x=-3..3);
Sol :=Bisection(f, x=[2.7,3.2],tolerance=10−2)
Sol:= 2.996875000
Bisection(f,x=[2.7,3.2],tolerance=10−2,output=sequence)
[2.7,3.2],[2.950000000,3.2],[2.950000000,3.075000000],[2.950000000,3.012500000]
,[2.981250000,3.012500000],2.996875000
Bisection(f,x=[2.7,3.2],tolerance=10−2,stoppingcriterion=absolute)
3.004687500
> Bisection(f,x=[3.2,4.0],output=animation,tolerance=10−3,stoppingcriterion=function_va
lue)
> Bisection(f,x=[2.95,3.05],output=plot,tolerance=10−3,maxiterations=10,stoppingcriterio
n=relative)
