Idea Transcript
How to Use Excel Solver Example problem: You want to maximize the number of calories you can grow in your garden, but you have a limited amount of land to use, and you must meet the minimum requirement for vitamins. You have three varieties of plants you can grow, and each requires a different amount of land and produces different amounts of calories and vitamins. Excel solver will help you decide the optimal number of plants of each type that you should grow in your garden.
Corn
Potatoes
Carrots
Calories produced per unit
100
150
35
Vitamins produced per unit 50
20
70
Land required per unit
0.15
0.07
0.1
Activate the solver tool. 1. Click the “Windows” button in the upper left hand corner of the screen. Click the “Excel Options” button in the bottom right hand corner of the window, near the exit button.
2. Click the “Add-‐ins” button on the menu on the left hand side of the window. At the bottom left of the window, there is a “Go” button. In the drop-‐down menu next to it, make sure “Add-‐ins” is selected. Then click the “Go” button.
3. Check the box next to “Solver Add-‐in” box and click “OK.”
4. In the spreadsheet, go to the “Data” tab. The “Solver” button should appear on the far right hand side of the ribbon at the top of the page.
Create your variables. In this problem, we want to vary the number of plants of each type that we grow in our garden in order to find the best combination. There are three types of plants, so we have three variables.
Create your parameters. In this problem, our parameters describe the properties of each variable. These things will not change as we look for a solution, but they will be part of the equations we use to calculate important numbers, like the total number of calories.
Write the equation for your objective. We want to maximize the total number of calories, so we need to write an Excel equation that calculates the total number of calories in the garden.
Write the equations for your constraints. We have two constraints: a maximum amount of land available and a minimum amount of vitamins to produce. So, we need one equation that calculates the total amount of land used, and one that calculates the total number of vitamins produced.
Define your objective in solver. Open solver by clicking the “Solver” button in the Data tab of your spreadsheet. The first field says “Set Target Cell.” In this field, you want the cell in your spreadsheet that contains your objective. You also need to indicate whether the solver should maximize or minimize your objective. In this problem, we want to maximize the number of calories, so choose maximize.
Define your variables in solver. The next field says “By changing cells.” In this field, you want the cells that contain your variables (in this case, the units of each type of vegetable).
Define your constraints in solver. The next field says “Subject to the Constraints.” Here, you add your constraints one at a time. First, click “Add.” In the “Cell Reference” field, put the cell that contains the formula you wrote previously. In the drop-‐down menu, choose the appropriate relationship. In the “Constraint” field, choose the cell that contains the limiting parameter. In this example, it says that the total vitamins produced must be greater than or equal to the minimum vitamins required.
Check the solver options. Check the solver options by clicking the “Options” button in the solver window. In this problem, we do not want any of our variables to be negative, so we need to check the “Assume Non-‐ Negative” box. The other options available in this window are not necessary for now, but it is helpful to know they are there.
Click “solve.” When you click solve, the values in the variable cells will change, and the formula results will change in response. If everything goes well, the solver results window will say that that solver found a solution.
If you run into problems, double check that all the formulas in your spreadsheet are correct and that you are referring to all the right cells in solver.