In previous screencast, we solved this problem where we have this fuel storage tank,

we made a live solution using the bisection technique shown down here.

In a different screencast,

I had shown how you can automate in VBA,

the Goal Seek Solver Tools to solve the same type of thing.

In this screencast, we are going to create a user defined function that's going to use

the bisection method to solve for the root of this equation.

In particular, we're going to make a user defined function called tank volume,

that's going to have arguments for,

we're going to be able to put in the radius,

the length of the tank,

but then we're also going to be able to put in the desired volume so over here,

I could put 100.

And when I press Enter,

it's going to automatically solve for the root of this equation using the volume here,

and it's going to use the bisection technique to do that.

Before watching the screencast,

you should brush up on the bisection technique.

There's a good screencast on implementing the bisection technique in a subroutine.

So let's go ahead and do this.

I'm going to make a function called tank volume.

It's going to have three arguments,

it's going to have the radius of the tank,

the length of the tank, and the volume.

And the output of the function is just going to be

the depth that certain volume takes up in the tank.

So what I'm going to do is I'm going to solve this using the bisection technique.

We dim I as an integer,

then we dim low,

high, mid, all is double.

We're going to need to calculate the function at low, and the function at mid.

So flow, and fmid as doubles.

We need an initial low and that's just going to be zero.

The minimum possible depth in this tank would be zero,

and the maximum the high would be twice the radius.

We're going to implement a for loop.

We're just going to do the bisection method 20 times.

Inside the for loop, we first define what the midpoint is.

And now, we need to calculate the functions of low and high.

So I've input the equation here to solve for flow and fmid.

Now this is just this equation,

this is the F of H equals zero,

type of problem and we have to make sure that we

subtract the total volume at the end of this.

So this is that big equation.

At the very end, we subtract the volume and that's input by the user,

appear as an argument.

Now we go through, we calculate the mid, flow, fmid.

Now we need to do our selection here, our selection criteria.

So we have a two way if then,

if flow times fmid is negative.

That means we have the solution on the lower half.

In that case, the new high is the old mid.

Otherwise, the solution is on the upper half,

and low is equal to mid.

So this is what happens in every iteration of the bisection method,

we go through 20 iterations.

Now at the very end,

we want to output the result,

and this is a function.

So the output of a function is the name of the function and we're just going to

do one last calculation of the midpoint which is the average of low and high.

Because at the end of each iteration,

we're not calculating a new mid.

And so, this then will spit out the corresponding depth.

So the depth that corresponds to a volume of Vol,

whatever the input as an argument and the radius and length.

The one thing I forgot to do is deal with units.

So the volume here is going to be in gallons,

and to convert gallons to cubic feet.

We divide by 7.48.

So now, we've taken into consideration the units for volume,

the output of the function is going to be right now everything is in cubic feet,

so I want to be in inches.

So the tank volume,

at the very end,

this is going to be in feet.

So I can just multiply by 12 to give us in inches.

So let's go ahead and see if our function works.

I'm going to put a breakpoint in my function,

I'm going to then type in equals tank volume,

we're going to use our radius that's we've defined on the spreadsheet,

the length of this,

and we're going to use the volume in the adjacent cell which is 100 in this case,

I press enter, we go into debug mode,

let's do F8 to step through here.

So I go through here a couple of times,

it looks like it's starting to converge on something or at least it's working.

And I'm pretty certain it's working,

so I'm going to remove this breakpoint and just continue,

and we go back to the spreadsheet,

and it gives us a height in inches with a volume of 100 gallons of 5.4 inches.

Now, just like other Excel functions,

we can double click,

we can drag that down,

and we get the corresponding depths to those gallons.

So this is exactly what we did and some of the other screencast in this module.

We've just implemented a user defined function.

And this is a real big hint,

because on assignment five,

you're going to have to do something similar to this by using

the golden search method. Thanks for watching.