This screencast is going to go over the bisection live solution method.
We're going to first explain how we can solve an example problem in Excel using the solver
tool, and then we're going to explain the limitations of that, and why we want to use
the live solution method of the bisection technique.
got a spherical tank that's going to be used to store a liquid, and the tank is made of
metal, we can't really see inside of it, so what we've got is this glass tube where we
can basically see the level of the liquid in the tank and what we want to do is we want
to set it up so that we can put markings on the outside of the glass so that somebody
looks at this they can see, you know, it might read 1000 gallons, and obviously because
it's non linear because of the spherical nature it's not going to be linear scale, so that's
what we're trying to do.
We can start this by developing equations.
Volume of liquid in the tank is given by this formula, where R is the radius of the spherical
tank, height is just the depth from the bottom.
The fundamental equation can be rearranged to the following f(x)=0 equal zero form.
What we're trying to do is provide V, and we're trying to calculate the height at which
that volume would occupy in the tank. this is a cubic equation.
It's fairly complicated to solve algebraically, it's going to have three roots, so let's consider
an example to investigate the nature of these roots.
Let's say we have a radius of 10 feet, and a liquid volume of 500 gallons, OK, and we
want to know what's going to be the depth of the liquid in the tank when we have 500
gallons in the 10 a foot spherical storage tank.
So we might plot that equation, height versus the function as a function of height, and
where that function equals zero is where we will satisfy our equation for that particular
volume.
So it's going to have three roots, one looks like it's about -0.5 meters, one about 0.5
meters, and another at about 9 meters.
The only feasible range is between 0 and about 3.05 meters.
Obviously there's only one feasible solution and we can neglect the other two roots of
this equation.
So there's multiple roots, but only one makes sense.
So if we confine ourselves to the feasible range from the empty tank to a full tank,
there's only going to be one root.
Let's use this in Excel.
Set it up in Excel.
I've got the radius, this is what we're going to input, the liquid volume.
So we want to know at what depth 1000 gallons occurs, so when we go back to that spherical
tank that's got the glass tube we can make a mark on there, kind of making a calibration
scale on that glass tube.
I have the equation here, this is what we're trying to solve.
We're trying to choose a volume and then determine the height at which that volume occurs, and
when that's satisfied this function of h will be zero.
So using solver I can put in this equation, OK, so I've just put in this equation here.
I go to the solver, and what we're trying to do is we're trying to set the objective
is cell C10, we're trying to set that to a value of 0, OK, when that's met for a particular
volume then we're at the correct depth.
I am going to be changing B7.
If you want you can put in some constraints here that the depth has to be greater than
zero.
I click solve.
So solver returns an answer, our function is very close to zero.
When we have 1000 gallons in the tank it's a depth of 0.65 meters, or about 2.14 feet,
but now let's say we want to make this more complex, instead of just knowing 1000 gallons
I want to look at a bunch of different depths, and I want to be able to do this pretty quickly.
I use the solver to calculate the height at which that occurs, or the depth.
I would have to copy that, "paste special", and then I go back and I want to find out,
OK what is it at 2000.
You see when I put in 2000 here it doesn't automatically reset this box to zero, right,
so I have to go back into data, I have to use solver, and I have to run this again.
So it runs it, it's saying at 2000 gallons I am at 3.08 feet, so I can take that, I can
paste over here.
So I could keep going on and on.
It's not going to be that hard if I only have ten different volumes, but if I want to do
several hundred I am going to have to do the solver several hundred times.
So let me show you a good way to do this in Excel, and this is called the bisection live
solution method.
If you're unfamiliar with the bisection method you might want to take a look at the screencast
on bisection, but essentially we're going to set it up to solve it using the bisection
method.
So I've got another spreadsheet here, and I've set up the bisection method here, I've
had 20 iterations, I've set up the first row starting with 0 to 6, and I've set this up,
I've got the if statements in there for recalculating the lower and upper bounds.
So when I copy this down and after I do 20 iterations it's going to converge on a depth
for that particular volume.
Right now for the volume I've got 300 gallons, and then I've set a pointer formula here for
depth, which is going to take the very end of our 20 iterations, so that's the zero of
this equation solved using the bisection method, and you'll see that it calculates a depth
of 1.15 feet.
So if I want to do I can take that and I could copy that, paste it over here for 300, paste
special, and then I could change this, and look what happens when I automatically change
the 100, what happens?
It's a live solution method, I didn't have to use solver, I didn't have to go into the
solver box and redo it because this bisection method does it automatically, and it does
it live.
So at 100 I could copy that if I wanted to, and I could paste special, but you guys all
know that if I want to do something like this with a lot of different input values I can
use the data table, so I am going to put a formula here, a pointer formula.
Where are we going to get result?
I want it in feet, so I am just going to reference that cell.
Now I am going to Ctrl+Shift+Down all the 313 or so different values for gallons.
I am going to go into "What If Analysis", "Data Table", and now we aren't going to have
a row input cell, we're going to have a column input cell.
Each of these values in our column is going into what cell?
Going into cell B5, and when I click go it's going to go through a bunch of different calculations,
it's going through the bisection method.
Each of these 313 values it goes through 20 iterations, and look how fast it does this.
For each of the volumes I've computed a height in feet, and if I wanted to I could just go
ahead and plot a graph of that, and I could give that table to some body and they could
go onto that glass tube of our original sphere, and they could put little markings at these
depths in feet that would correspond to different volumes.
Even though solver tool can solve a lot of things in Excel, it can't do something sophisticated
as this, and you can use something of the bisection method.