This video is on bisection and Microsoft excel 2010. For this bisection video I have an example
problem. The problem is 0.5 times exp(x) minus 5 times x plus 2. With this function we need
to find the zero of it, or where it crosses the x-axis. I know that there are two of them.
One is between 0 and 1 and the second is between 3 and 4. We are going to try and find the
one between 0 and 1. To set this up we need to do the same thing as we would if we were
doing it on paper. We need to have 2 initial guesses. I am going to call them guess "a"
and guess "b". Guess "a" I am going to pick as 0 and guess "b" is going to be 1 since
I am aiming for that first root. Now I need to have the midpoint between them which is
"c". 0, 1 and half way between them is 0.5. Now we need to plug each of these into this
function and find out their values are. So we have f(a), f(b) and f(c). f(a) is equals
0.5*exp(0)-5*0+2. The values it 2.5. I can use the fill handle to go ahead and fill in
f(b) and f(c). Now we need to find out what our new "a" is. To help visualize this I have
a drawing. Guess "a" is 0 and guess "b" is 1. Our guess "a" is here and our guess "b"
is here which means our midpoint is about here. So our midpoint is a positive number.
It is 0.3. Guess "a" is a positive number at 2.5. Guess "b" is a negative number at
-1.6. As you can see both "a" and "b" are too far off of the screen. What we want to
do with bisection is to get closer and closer to our 0 here by trimming off the excess on
the end. We can tell by looking at this that we need to get rid of the "a" and replace
it with the "c" since the "c" is much closer to the zero than the "a" is and they are both
still positive. Since the "b" is negative and the only negative we are going to keep
that the same. How do we translate this decision into the code needed in our excel spreadsheet?
We need to find out mathematically here if the sign of "c" which is our midpoint is the
same as the sign of "a" or the sign of "b. If it is the same as one of then it replaces
the original. For example we would have to use an "if" statement. If the logical test
which in this case is that times that is greater than or equal to 0 then this becomes its new
value. If not it will keep its old value. What this is saying is if f(a) times f(c)
is greater than or equal to 0, if they are the same sign, then this becomes cell c7.
The midpoint would replace guess "a". If they are not the same sign then guess "a" would
remain the same. We need to do the same for this test. If f(b) times f(c) is greater than
or equal to 0 the midpoint will replace guess "b". If not guess "b" will remain the same.
The midpoint is just finding the location between the two so we can use the fill handle
to fill that in. As you can see guess "a" is now 0.5, guess "b" remains the same as
1 and our new midpoint is 0.75. The three of these just fill down. Now we have an entire
row here of referencing. If we want we can fill down a bunch of rows until we get a satisfactory
answer. At this point we need to decide how we want to finish this. Do we want to go with
a certain number of times. For example iterate it 20 times or do we want to go until it reaches
a specific accuracy. I am going to go ahead and run this about 10 to 20 more times. I
am going to make this simple and just go ahead and make this recalculate Here you can see
that the next guess had our midpoint as a negative value. The 0.75 became guess "b"
and we added a new midpoint. If I recalculate it again it will be 0.625 for our guess "b".
The gap between guess "a" and guess "b" is getting smaller. I am going to create another
value here that is just the gap between these two. That way we can see how close they are
getting. I will take this and fill it down another one. Again you can see how small that
error is getting between them. I can continue to do this until either these stop changing
or my error, which is this final column, gets within a specific range. Let's say I want
my error to be less than 0.00005. I will just have a simple test here. If that is less than
0.00005 then I will have it give a 1 for true or a 0 for false. Until it shows a 1 I know
that it has not reached a high enough accuracy. Here we have reached a 1 so I know this point
has already been below my estimated error. This means for my calculation here I can take
this midpoint value which is 0.5783 and say that is roughly the root of this function.
You can do this if you like and switch this to 3 to 4 and have it run again. It will reach
the new point at 3.4018. This is the second root. It will just recalculate when you change
these. I can change this to 3.5 and it will get there faster by one step. If I change
this to 3.3 then it will get there faster by here. It is up to you how you ultimately
set this up but this is a basic bisection.