Define and solve a problem by using Solver

Microsoft Office Excel 2003

Show All Show All

Define and solve a problem by using Solver

  1. On the Tools menu, click Solver.
  2. If the Solver command is not available on the Tools menu, you need to install the Solver add-in program.

    ShowHow?

    1. On the Tools menu, click Add-Ins.
    2. If the add-in you want to use is not listed in the Add-Ins available box, click Browse, and then locate the add-in.

    3. In the Add-Ins available box, select the check box next to the add-in you want to load, and then click OK.
    4. If necessary, follow the instructions in the setup program.
  3. In the Set Target Cell box, enter a cell reference or name for the target cell. The target cell must contain a formula.
  4. Do one of the following:
    • To have the value of the target cell be as large as possible, click Max.
    • To have the value of the target cell be as small as possible, click Min.

    • To have the target cell be a certain value, click Value of, and then type the value in the box.

  5. In the By Changing Cells box, enter a name or reference for each adjustable cell, separating nonadjacent references with commas. The adjustable cells must be related directly or indirectly to the target cell. You can specify up to 200 adjustable cells.
  6. If you want to have Solver automatically propose the adjustable cells based on the target cell, click Guess.

  7. In the Subject to the Constraints box, enter any constraints you want to apply.

    ShowHow?

    ShowAdd a constraint

    1. In the Solver Parameters dialog box, under Subject to the Constraints, click Add.

    2. In the Cell Reference box, enter the cell reference or name of the cell range for which you want to constrain the value.

    3. Click the relationship ( <=, =, >=, Int, or Bin ) that you want between the referenced cell and the constraint. If you click Int, "integer" appears in the Constraint box. If you click Bin, "binary" appears in the Constraint box.

    4. In the Constraint box, type a number, a cell reference or name, or a formula.

    5. Do one of the following:

      • To accept the constraint and add another, click Add.

      • To accept the constraint and return to the Solver Parameters dialog box, click OK.

    Notes

    • You can apply the Int and Bin relationships only in constraints on adjustable cells.

    • When the Assume Linear Model check box on the Solver Options dialog box is selected, there is no limit on the number of constraints. For nonlinear problems, each adjustable cell can have up to 100 constraints, in addition to bounds and integer restrictions on the variables.

    ShowChange or delete a constraint

    1. In the Solver Parameters dialog box, under Subject to the Constraints, click the constraint that you want to change or delete.

    2. Click Change and then make your changes, or click Delete.

  8. Click Solve and do one of the following:
    • To keep the solution values on the worksheet, click Keep Solver Solution in the Solver Results dialog box.
    • To restore the original data, click Restore Original Values.

    ShowTip

    You can interrupt the solution process by pressing ESC. Microsoft Excel recalculates the worksheet with the last values found for the adjustable cells.