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.