So you're probably guessing that we are going to implement
the golden search technique into Excel to make a life solution.
And that's exactly what we're going to do in the screencast.
So I've set this up. This is a modification of
a spreadsheet that I showed in one of the earlier screencast.
And what I've got,
I've changed the problem statement around a little bit.
Instead of having 2640 ft for a half mile of the swamp in the forest and exactly one mile
for the width here.
Instead, what I'm doing is I'm turning that into S for the swamp.
We can change that distance F for the forest
and D for this total distance here to the getaway car.
Now the equation for that is shown here,
and I have put in cells up here in a name,
the cells appear in the name box S,
F and D. And I've put in a function here for the time.
So this is just this formula here that's going to give us
the time depending upon these different parameters.
But that's just the time for the given theta here that I guess there.
And you see that when I change theta,
I change the time.
So just like we did before there's going to be some sort of
optimal angle that the escapee can
choose and that's going to determine then through
this equation the time that it takes to get to the getaway car.
So as I showed in a previous screencast,
we can solve this scenario using the solver tool.
So we're trying to set the time to a minimum by changing the angle here.
And when we do that, actually I don't think this is quite right.
C2 we want that to be the time to a minimum by changing the angle.
And I press solve it,
turns through and finds a solution.
So with these particular parameters,
the convict should choose an angle of 65 degrees to get through the swamp and the forest.
And again the swamp he can travel two feet per second,
in the forest he can travel four feet per
second and that's where we get the two and the four from.
But we want to make this so it's a live solution.
In other words if I change s to something like 3000 then it'll automatically change.
You see that it automatically changes
the time but it doesn't automatically adjust the angle.
In order for us to adjust the angle such that we minimize
time we have to go back up here into the solver tool and we have to click solve.
So this is not a live solution and to make it a live solution,
we're going to implement the golden search technique.
So I've got this set up to put in our golden section search algorithm.
I've got my golden ratio up here which is
the square root of five minus one quantity divided by two.
I've named that GR up here in the name box.
Now when you're setting this up you always have to
sort of realize that these values here,
the a b d x1 x2 correspond to the independent variable that you can change.
So it can independently change that has an effect on the output.
So the thing that you can change the thing that you are
adjusting is theta which is the degrees.
So that's the independent variable in this function here.
And then the output is the time.
So, the minimum theta,
theta in this example would usually be I guess between
the smallest you could ever have would be
zero and the largest you would ever have would be 90.
And so I'm going to put this in in terms of degrees.
And it actually turns out that the sign of zero radians or degrees is zero.
So in here we would be dividing by zero.
So I'm just going to make this I'm just going to use one degree.
D is equal to the golden ratio times b minus a. x1 is equal to a plus
d. x2 is equal to b minus d. f(x1) is just taking x1.
Remember x1 x2 ab these are thetas.
So I'm plugging that,
I'm plugging x1 into this function above making sure to convert
the degrees because these are all in degrees to radians using
the radians function and we get f(x1).
And I can just copy this control C and paste it there.
We have our conditional statement to set the new a in the next iteration.
So if f(x1) is less than f(x2),
we're going to be cutting out a portion on the left part,
and in that case if that's true then the new a is going to be x2.
Otherwise, it's just going to be the old a.
And now I can put my conditional statement in here for the next b.
So, if f(x1) is less than f(x2),
again we're removing part on
the left side and in that case the new b is just going to be the old b.
Otherwise, it's going to be x1.
Now, I can just take this part.
This is all just relative formulas,
and I can take the entire row here and I can double click.
And you notice that as we get down towards the tenth iteration,
the average of x1 and x2 is a good approximation of the result.
I think we should probably go.
The precision here is not what we would want.
So I'm just going to go another 10 iterations which is really easy to do in Excel.
I'm going to double click on this.
And you see after total 20 iterations we're getting down to
something that's quite reasonable, 65.51.
Now, what I'm going to do up here,
I'm going to go ahead and erase these because we're not using those anymore.
And I'm going to put a pointer formula.
The result here is going to be the average of x1 and x2 at the last iteration.
So I'm just kind of making a pointer formula and the time is going to be
the average of f(x1) and f(x2).
Now these are in seconds.
So I'm going to do the average of that but then I'm going to divide
by 60 to convert 2 minutes.
And that's exactly what we got using the solver tool.
So now for the really exciting part,
remember that the solver tool is not a life solution.
If you change the different parameters using the solver tool,
it does not automatically update the theta.
It'll update the time because that's just a formula here.
But then you have to rerun the solver in order to minimize theta.
What we can do using this technique is I can,
it's a live solution so I can automatically change these values in the spreadsheet.
When I press enter, it goes through 20 rounds of
the golden search technique and then it's going to find
the angle theta that minimizes the time.
So when I press enter,
it automatically goes through that.
And I can change other things,
I can change this to something like 3500
and it automatically calculates the new solution.
So it's a live solution of an optimization problem
similar to what we did using the live solution of the bisection method.
And this does not use any sort of visual basic.
Hope you guys enjoyed. Thanks for watching.
Charlie NuttelmanInstructor
