• Home   /  
  • Archive by category "1"

Solve Assignment Problem Using Solver In A Macro

All right. I'm going to explain what an optimization problem is in this screencast.

Engineers and scientists and mathematicians oftentimes are trying to optimize a process.

And optimizing either means maximizing or minimizing.

So let's go through exactly what an optimization problem is.

So similar to a targeting problem,

an optimization problem has an input cell and an output cell.

So, for example, we might have an input of 2.5.

And somehow, through a mathematical relationship,

the output cell depends directly on the input cell.

So it's some function.

The output cell is some function of the input cell.

So we might have x and f of x.

Now the target is the output cell,

and that's what we're trying to optimize.

We're either trying to minimize that or maximize that.

So the goal is to maximize or minimize the target which is f of x.

Now, we can do this in Excel using

the Solver tool I showed in a previous screencast on targeting problems,

that we can use the solver to set a value equal to some target.

But we can also minimize or maximize a certain cell with a given input.

So let's go through an example. I have an example here.

We wish to find the max and min of this function,

f of x, in the range 0-10.

Similar to a targeting problem,

we need to come up with some sort of initial estimate or initial guess.

So I've got this problem set up in Excel.

We're going to need to start with an initial guess.

So I'm just going to say 5, and we're going to calculate the minimum and maximum.

Now, before we do this,

it's nice to make a plot of your function.

So let's go ahead and do that for 0-10.

So I've typed in my function here as a function of x.

And when I press enter,

then that gives me my function value.

I can double click here to bring that down.

I'm going to go ahead and select all of these and go into Insert,

Charts, and I'm going to just make a simple line graph.

So this is kind of showing us our function.

Now, we want to find the minimum and maximum in this region.

Now, the minimum is going to be here at about 2.5, x equals 2.5.

The maximum, depending upon how we do things,

could either be here at about 5 and a half,

or it might find the maximum at 0,

which is the lower end of our domain.

So in my main cell here,

I'm just going to go ahead and type in the function.

So I typed in my function referencing the x.

And right now, at a value of 5, we are calculating.

And I'm just going to change this actually to f of x.

So our function value is 0.24.

And now we want to find the minimum.

Now the Solver tool,

just like the Goal Seek tool,

needs initial guess, which we've already got.

So I'm going to go ahead and go into Data,

Solver, and I'm going to set objective,

which is going to be f of x,

and we're going to set that to a minimum by changing variable cell B2.

So let's go ahead and do solve,

it churns through and finds that there's a minimum at about x equals 2.51,

which is the y value or the function value of about negative 0.6.

I want to show you how we can change the minimum that we

find by changing the initial guess.

So you see there's another minimum here,

at about x equals 9.

If we guess around 9,

then we should get the minimum closest to 9.

So I'm going to change my initial guess to 9.

We can go back into the Solver tool.

The nice thing about the Solver tool is it remembers scenarios.

So it remembers everything we did.

So I just have to do solve,

it churns through and finds the minimum closest to 9,

which is 8.8 with a y value of about negative 0.17.

Now, let's use a Solver tool to find the maximum.

And I'm going to put in a value of 6 here,

and we're going to run the Solver tool.

Instead of calculating the minimum,

we're going to select max and we click solve,

it comes through, and it finds that maximum at about 5.65.

Now, if we put the initial guess very close to zero,

so let's do this.

Let's do closest to zero.

Let's do our Solver tool,

click OK, and it finds a value of zero.

And the reason that it did this,

it found for the value where x equals zero,

is right now in our Solver tool.

I have this box here selected that says,

Make Unconstrained Variables Non-Negative.

So if I deselect that,

then I can allow for negative values of x.

So let's go ahead and rerun it.

So now, it goes through,

and if I would have plotted negative numbers here,

there would have been another max somewhere at about x equals negative 0.62.

All right? So that's what it's going to find.

Now if we wanted to force a certain domain into our Solver box,

I can go into Solver,

and this is where we can add in our constraints.

So I can click on Add.

We want to make it such that B2 has to be less than or equal to 10.

So that's our upper domain.

So I can click Add,

and then my B2 also has to be greater than or equal to zero.

So that's how you can add constraints into your solver scenario.

And now, I can click Run.

And you see now with negative values of x

allowed and adding in a constraint that x has to be between 0 and 10,

it's finding that the maximum value closest to 0 is about 0.91.

Let's go through another somewhat creative example.

You have just escaped from prison.

So you are right here at the 0,

0 position, and you've got a getaway car waiting for you.

That's one mile north and one mile east of where you are.

So you've got to get a getaway car and a friend maybe right there.

You've got a swamp that you have to travel through.

And you can only go two feet per second through

the swamp and you can only go four feet per second through the forest.

So you can go twice as fast through the forest.

And so, obviously, depending upon the angle that you choose to begin with,

that's going to govern the time required to get to the getaway car.

So if you can go very slowly through the swamp,

then maybe you want to make this distance pretty short.

So you might want to make this as short as possible.

But the shorter you make this leg of the trip,

then the longer the second leg of the trip is going to be.

So this is an optimization problem,

and this is a problem that I give to my students every year.

If you set up an equation for this,

you end up with the following.

And this is just through simple geometry.

The time it takes for you to get to the getaway car is a function of the angle,

which is the independent variable.

The angle is expected to be between 30 and 90 degrees.

If you guess a theta,

then you can plug it into the right hand side,

and that'll give you the time required at that

chosen theta that it will take you to get to the getaway car.

So this is an optimization problem that we can solve using Excel.

So I've set up my spreadsheet here.

Solver needs an initial guess.

So I'm just going to choose something between 30 and 90 degrees,

a nice round number of 45.

And now, I can use this equation here to calculate

the time in seconds that is going to take to get to the getaway car.

So I've got my guess of 45 degrees.

I've set my equation here and note that I have used the radians function because cell B2,

our angle is in degrees.

We have to convert that to radians,.

A good way to do that is to use the radians function because

the SIN and TAN functions in Excel need radians as their units.

So I've got this in here. I press enter.

So if you go at a angle of 45 degrees,

it's going to take you 2800 seconds.

Let me quickly convert this to minutes by dividing by 60.

We can use the Solver tool.

Now, we're going to set the objective,

so B3 to a minimum value by changing cell B2.

And when I click solve, it churns through,

and it determines that if you go at an angle of 65 degrees,

that's going to get you to the getaway car the fastest,

after about 44.4 minutes.

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.

One thought on “Solve Assignment Problem Using Solver In A Macro

Leave a comment

L'indirizzo email non verrà pubblicato. I campi obbligatori sono contrassegnati *