Okay, in this screencast,
I'm going to explain all about what targeting problems are.
Targeting problems are quite common in math and science and engineering fields.
So, a targeting problem is when you generally set up a spreadsheet,
and you've got an input cell, we might have any,
a value of six in our input cell,
and then we have an output cell.
I don't know, maybe we have an output cell of 27.2,
and somehow these are linked.
So, the input cell directly
affects the output cell through some sort of mathematical relationship.
And it doesn't have to be a simple formula,
it doesn't have to be a direct formula,
it can be through a bunch of different linked cells on the spreadsheet,
but in general, we have an X,
and we're going to call that our input,
and we have an output,
which we're going to say is a function of X,
so f of x.
Now, targeting problems are when you're trying to drive f of x equal to some value.
A lot of times, you're trying to drive f of x to zero and in that case,
you're solving for the root of an equation,
but it doesn't have to be zero.
You can also solve for a different target.
For example, you can set that f of x equal to a value such as five.
This is known as a targeting problem.
The target here is going to be the output cell,
and let's just say in this example,
we want the target to be zero and we want to change the input cell x.
Let me go through an example.
Here, we have just a mathematical equation,
and we wish to find the root of this equation,
and the root is the value that makes the left hand side equal to the right hand side,
which is the value of zero.
So, once we have found the root,
the left hand side is equal to zero.
There's a tool built into Excel that quite a few of you are
probably already aware of and that's the Goal Seek tool,
we can use this to solve non-linear equations.
The way Goal Seek works is you start with an input cell,
and you calculate the output,
and this is usually through some sort of mathematical relationship, and then you compare.
You compare the function value f of x to some target,
and a lot of times,
the target is zero, but the target does not have to be zero.
You compare to see how close those are,
and that's done using this tool called Goal Seek,
and when the f of x and the target are sufficiently close,
then the Goal Seek terminates this process.
However, if the f of x and the target are not close to one another,
then the Goal Seek tool adjusts x such that it changes the output f of x,
and the Goal Seek tool keeps iterating until the difference between
the function and the target value are minimal within some tolerance value.
In our example, we could go ahead and do this in Excel.
So, I want to show you how we can do this in Excel.
Importantly, we also need an initial estimate for x.
So, you need some sort of initial guess.
So over in Excel, I've set up my input cell, and I'm just guessing.
So, I'm just guessing it an initial guess of five and in my output,
then I can type in a formula for our function.
So, I've typed in the formula down here,
and when I press Enter,
we're calculating f of five because this is referencing cell C2,
this is referencing our initial guess.
And I'm not at my target yet because I want this target to be zero.
If I wanted to, I could manually change x to see what effect that has on the output,
and without too much difficulty,
you could narrow this down,
and we could find something that's quite reasonable.
So, it's not that hard,
but as you get more and more complex functions that depend upon different things,
then this becomes harder to do.
And it's a lot faster if you just automate it using the Goal Seek tool.
So, I'm going to use the Goal Seek tool.
We want to set cell C3 to a value of zero by changing cell C2.
So up here on the Data tab over in What-If Analysis,
I can click that, and there's this tool called Goal Seek.
And Goal Seek is just a pretty simple tool.
You can set any cell to a value by changing a different cell.
So, I'm going to set my target cell.
My target cell is going to be C3.
I want that to be a value of zero,
and I'm going to be changing cell C2.
And when I click OK,
it automatically churns through different scenarios and zeroes in on the solution.
So when X is 0.783,
then that satisfies this relationship down here.
The output is zero or close to,
and so we found the root of this equation.
Let's go through one more example using
estimates of rainfall, evaporation, water consumption.
The town engineer developed the following model of
the water volume in the reservoir as a function of time.
So, we've got this equation.
And what we're trying to do is,
how long will it take until the reservoir is empty?
So we want to solve for the time at which the volume is empty.
Now some of you might know some advanced math skills,
you might be able to do this by hand analytically.
Unfortunately, a lot of us forget
our basic math skills and this I think is actually quite hard to solve.
So, we use a computing tool.
This is perfect for using the Goal Seek tool in Excel.
So, we want to adjust T until the volume is equal to zero.
So, I've got this set up in Excel,
you always need an initial guess,
so I'm just going to say something like 50 days,
and now I'm going to plug in the equation for volume,
so here's my equation.
And when I press Enter, this is the volume in liters.
And again if you wanted to,
you could adjust this and just see what happens.
If I go to 200 days, it becomes negative, 100 days,
and so on, you could just manually do that or you can use the Goal Seek tool.
We're going to set cell B4 to a value of zero.
We want to figure out when there's going to be zero left.
It doesn't have to be zero, I could say
when is there going to be a million liters left or
a billion liters left by changing the time.
And when we do that, it cranks through, it finds a solution.
It says about 106.5 days until this reservoir will be empty.
We can also use,
there's a Solver tool over here on the Data tab,
on the data ribbon.
If that's not showing,
you can always go into File, Options, Add-ins,
and then you can go down here and click Go,
and you can click the Solver Add-in.
So, let's go ahead and run the Solver.
The Solver tool does the same thing.
Solver tool has a lot more stuff, we'll talk about,
it's more important and more valuable for doing optimization scenarios.
But we're going to set the objectives,
I'm going to set objective the volume equal to not a max
or a min but to a value of zero by changing variables cell,
that's going to be B3.
You noticed that in the Solver tool,
you can change multiple cells.
You can also add constraints,
which can be important.
So, I'm going to just do that.
We're adjusting the volume until it's equal to zero by changing the time.
And we crank through this and it comes up with the same solution.
Charlie NuttelmanInstructor
