Bisection Example/Excel

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.

test attribution text

Add Comment