Microsoft Office Tutorials and References
In Depth Information
USING EXCEL SOLVER
Solver Parameters window
The Solver Parameters window in Excel 2010 looks intimidating at first. However, to solve linear
optimization problems, you only have to satisfy three sets of conditions by filling in the following fields:
Specify the Optimization Cell.
By Changing Variable Cells
Specify the Changing Cells in your worksheet.
Subject to the Constraints
Define all of the conditions and limitations that must be met when
seeking the optimal solution.
The following sections explain these fields in detail. You may also need to click the Options button and
select one or more options for solving the problem. Most of the cases in this topic are linear problems, so you
can set the solving method to Simplex LP, as shown in Figure D-17. If this method does not work in later
cases, you can select the GRG Nonlinear or Evolutionary method to try to solve the problem. Note that
solving methods are only available in Excel 2010.
Optimization Cell and Changing Cells
To use Solver successfully, you must first specify the cell you want to optimize
in this case, the total
shipping cost, or cell N21. To fill the Set Objective field, click the button at the right edge of the field, and
then click cell N21 in the spreadsheet. You could also type the cell address in the window, but selecting the
cell in the spreadsheet reduces your chance of entering the wrong cell address. Next, specify whether you
want Solver to seek the maximum or minimum value for cell N21. Because you want to minimize the total
shipping cost, click the radio button next to Min.
Next, tell Solver which cell values it will change to determine the optimal solution. Use the By Changing
Variable Cells field to specify the range of cells that you want Solver to manipulate. Again, click the button at
the right edge of the field, select the cells that contain the numbers of trucks (H16 to H20), and then hold
down the Ctrl key and select the cells that contain the numbers of tractor-trailers (J16 to J20). If you used a
fill color for the Changing Cells, they will be easy to find and select. The Solver Parameters window should
look like Figure D-18.