Microsoft Office Tutorials and References

In Depth Information

**USING EXCEL SOLVER**

FIGURE D-17

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:

Set Objective

—

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.

—