In this screen cast, I'm going to show you another example of using

the Goal Seek and Solver tools to solve a nonlinear problem in Excel.

I'm also going to show you why

the Goal Seek and Solver tools have limitations

especially when we start to look at case studies,

and that is, we have many different equations that we're trying to solve.

If you rely upon the Goal Seek and Solver tool,

then you are going to have to use the Goal Seek and Solver tool many many times.

So I'm going to show you some drawbacks of using

the Goal Seek and Solver tools in this screen cast.

So you are an owner of a gas station,

and every morning you've got to go out and put this dipstick down into

the ground and into your tank where your fuel is stored.

Now it's a non-linear problem in that

an inch increment on this dipstick at the very bottom of the tank

corresponds to a lot less volume than

an inch increment in the middle of the tank because of the way that the cross section is.

The tank is a cylindrical tank,

so the cross section is a circle but then it's got these hemispherical ends.

If we looked at the cross section,

you might have fuel in the bottom that takes up a depth h. And

what you want to be able to do is create this calibration scale that I've shown here.

You want to be able to come up with some sort of

mathematical relationship so that given nice round numbers of volume like 100, 200,

300 gallons, you want to know how far from the end of the dipstick to put

those markings and this will set up your calibration scale

that so that when you put this dipstick down into the tank,

it will readily give you the volume.

So I've got these two equations here,

the volume of the spherical part.

So you have two half spheres combined,

give you a sphere, and we've got the volume of the cylindrical part.

It's a pretty complex equation trying to solve either of these equations for the height.

So an explicit formula for height would be pretty difficult.

So what you want to do is be able to put in a volume of for example 100 gallons,

and what is the height on this dipstick or what is

the depth little h that corresponds to that 100 gallons?

And you then do this for a bunch of different values of the volume.

So the total volume is equal to the volume of

the cylindrical part plus the volume of the spherical part.

And we're going to use the Goal Seek and Solver tools to back

calculate out what the height is that corresponds to a certain volume.

So I've got this set up in Excel.

I've got my two equations.

I have my length and I've named that L up here in the name box.

I've got the radius which I've named radius up here.

Also for each iteration,

for each volume that we're going to back-calculate

out the depth that corresponds to that volume,

I need to have a guess.

So I've just guessed for example here 10 inches and

I've converted that 10 inches to feet by dividing by 12.

And I've also named that cell little h up here in the name box.

So in these two cells,

I put in the formulas for volume

of the cylindrical part and the volume of the spherical part.

So those are in terms of my named variables on the spreadsheet.

I've also computed a total volume and that's just the sum of those two.

And finally, what we really want to do is have

the input be in gallons and we want the output to be in inches,

so that's why I'm doing these two conversions.

So in this cell,

I've multiplied by 7.48 to convert cubic feet into gallons.

So I just put in a little.

I started a table down here.

I've got my volume of 100 gallons and we want to know how far from

the end of the dipstick should we put that marking for 100 gallons.

What I can do is I can use the Goal Seek tool.

So I'm going to adjust little h which is cell B 6,

until the volume total in gallons B13 is equal to 100.

So I'm going to go up to the Data tab,

What-If Analysis and do Goal Seek,

and I'm going to set cell B13 to value of 100 by changing cell B 6.

So that's our depth in inches.

And when I do that then it turns through and it says that at 5.4 inches,

that's going to be 100 gallons in our tank.

So I'm going to go ahead and copy this and paste it down here

and I could do the same then for 200 gallons.

I can go up here to what if analysis, goal seek,

set cell B13 to 200 by changing cell or depth again in inches and it goes through,

it finds a solution,

I can copy that and I can paste the value there.

All right. So this is doable and it works just fine.

However, if we wanted to go up through the entire volume of the tank, by the way,

I can easily calculate the total volume of the tank by putting in for the depth.

I could put in six feet because the radius is three and six feet is 72 inches.

When you do that, we end up with about 3,400 gallons.

So if I want to put markings on our dipstick every 100 gallons up to the total,

I could drag this down to just under 34, so 3300,,

and then I would have to use the Goal Seek tool and other,

I think I've got another 31 of these.

So it's doable. I would just be using the Goal Seek tool quite a bit.

We can also solve this scenario using the Solver tool.

The Solver tool is a little easier to use for

multiple scenarios because it stores our previous scenario.

So I'm going to go up here to the Solver tool.

I've reset my guess to something more,

something smaller like 10. I'm going to go to Solver tool.

I'm going to set the objective, the volume here,

to a value of 300 by changing the variables cell depth in inches,

and when I go through that,

it solves and it says that we have that corresponds to 11 inches.

So I could copy that,

I could paste down here, and I could keep going.

Now the nice thing about the Solver tool,

as I just alluded to,

is that it remembers scenarios.

So, I can just go back up to the Solver,

it remembers everything I did.

Now I can just change this to 400.

Then I could do that, then I can take the depth here of 13.3,

copy and paste.

So that's doable.

However, doing this technique using

either the Goal Seek tool or the Solver tool is going to take us a lot of time.

So, I still have another 29 or so different values that I want to put into my case study.

Furthermore, we'll see in subsequent screen casts,

this is not a live solution.

So if I change the length of the tank,

so maybe I wanted to do,

what happens if the length is 10?

You notice that these don't automatically update.

These height values, these depth values that we calculated don't automatically update.

So this is not what is known as a live solution.

If I change the radius,

the result here that we calculate is not computed in a live fashion.

So it's not a live solution,

and I'll show you how we can make life solutions later on in this module.