In this screencast, I'm going to show you how to use the Solver with constraints. A lot of times, you'll have two things that you're trying to satisfy. For example, you're trying to minimize or maximize something but you're also subject to another constraint, another objective. And so, I've got two really good examples in this screencast that are going to demonstrate how to use the Solver tool with constraints. The first example that we're going to be working through, we're trying to design a conical tank, a conical bin with a top. We've got a conical tank here. Then we've got this circular part, which is going to provide the top to this tank. So this might be something like a hopper where you're storing something in there. And what we're trying to do is we have two goals. We want to make sure that the bin volume is 1 cubic meter. But we also want to minimize the total surface area of this conical bin. The cost of this conical bin is going to be proportional to the surface area of the bin. We could kind of look at the extremes if we made a tank that was really long and skinny, shown here on the left versus this one on the right, which is very fat and wide. Both of these tanks might have a volume of 1 cubic meter, but there might be a tank kind of in between these that has a little bit less surface area. So if you make the bin really long and skinny on the left or really fat and wide on the right, it's going to have a lot of surface area. And so therefore, it's going to cost a lot. So we're trying to do is minimize the total surface area, but we have a constraint that the bin volume is 1 cubic meter. Let's take a look at the equations. The volume of a cone is given by 1/3 pi r squared h. And if you don't know where these come from, you can always just find them on the Internet. The surface area of the outside of a cone is given by this second formula here, a of the sides and the top is just going to be a circular part. The area of that top is just pi r squared. The total surface area then is the sum of the top and the area of the side. Let's go ahead and solve this in Excel, and we're going to be using the Solver tool to do this. I've got this in a file called Solver with constraints. I've got all the equations here. I've got the kind of a drawing here, which I like to do. We want to create create this cone to have a volume of 1 cubic meter. We need to guess, we're going to have to guess the initial radius and height that the solver is going to use then to converge upon our solution. We wouldn't want to use something ridiculous like 1000 meters for this tank that's going to have a volume of 1 cubic meter. You want to kind of base your guesses on the realistic constraints, the physical constraints of this system. So maybe a radius of 1 and a height of 1 might be good initial guesses for the dimensions of this tank. I'm going to go ahead and name all these variables here in one fell swoop. So I can select that, go up here to Formulas, Create from Selection, I'm going to go ahead and click OK. And so, we've got everything named. I'm going to use these formulas here to compute the area of the side, the area of the top and the total surface area. So, the A_side = pi times our radius times the square root of the radius squared + height squared. When I press Enter then, it's calculating the surface area of the side of this conical tank to be 4.44, let's do the top. So I'm just going to plug in this equation for a circle pi times the radius squared. The top then has a surface area of 3.14 when we choose a tank that has a radius of 1 and a height of 1. The total surface area then is just the sum of the side and the top, I'm just summing those. We're going to now finally calculate the volume here. The volume is given by this formula at the top here, so of 1/3 pi times the radius squared times the height. And given these dimensions that I've guessed, we're getting a volume that's pretty close to 1 cubic meter. Now, we want this to be exactly 1 cubic meter and we want to minimize the total surface area. So we actually have two objectives here. We have an objective to minimize the total surface area and we also have a constraint or a target that the total volume has to be 1. You can't solve a problem like this using Goal Seek because the Goal Seek tool is only for a single target. The Goal Seek tool can't even be used for optimization, it's just for targeting problems. So I'm going to go up here to the Data tab > Solver tool and we have our objectives and our constraints. Whenever one of your objectives is an optimization, you have to put that at the top here as your primary objective. In other words, you can't have this upper objective a target and have the constraint and optimization. The optimization constraint has to be up here as the objective. So, we're trying to minimize total area. So I click on B11. We want to minimize that by changing variable cells. Now, the Solver tool, you can have multiple inputs. Here, we have the radius and height as inputs. We can independently change those. So I'm going to select on B5 to B6. And now, this is where in the subject to constraints, is where we're going to put in the second objective or the constraint that the volume has to be a target of 1 cubic meter. I'm going to go ahead in here, click Add. Our cell reference is going to be our volume, B13 is equal to, I can select the different things from this drop-down list, is equal to, and the constraint is that it has to be equal to 1. And then I go ahead and click OK. if you had more constraints, you would click Add to add more constraints, and then, I can go ahead and click Solve. I did want to mention one thing. It doesn't happen a whole lot. It does happen in some areas of engineering where you're solving for something that is negative. If your variables that you're trying to solve for can be negative, you want to make sure that you deselect this checkbox because this makes unconstrained variables non-negative which means positive. So just something to be aware of. In this case, we don't have to do that because our radius and height are going to be positive numbers. I can go ahead and click Solve, it goes through it. And it says that it found a solution when the radius is 0.7 and the height is 1.97, it's minimized the total surface area and the volume is 1 cubic meter.
