Using Solver

Excel 2016 2013 2010 2007
Solver tool allows you to specify multiple adjustable cells and constraints on the values that the adjustable cells can have, generate a solution that maximizes or minimizes a particular worksheet cell, generate multiple solutions to a problem.

For example, let's analyze sales of three products. Column B consist the number of units of each product, column C shows the profit per unit for each product, and column D contains formulas that calculate the profit for each product:

Example in Excel 2003

In order to maximize total profit, Company has some constraints:

  • The combined production capacity is 200 total units per day.
  • The company needs 50 units of Product A to fill an existing order.
  • The company needs 40 units of Product B to fill an anticipated order.
  • Because the market for Product C is relatively limited, the company doesn't want to produce more than 40 units of this product.

To solve this task, do the following:

   1.    Set up the worksheet with values and formulas. Make sure that you format cells logically; for example, if you cannot produce partial units of your products, format those cells to contain numbers with no decimal values.

   2.    Select Tools -> Solver... (Solver is an add-in, if you can't found it, see How to manage Add-Ins) to open the Solver Parameters dialog box:

Solver Parameters in Excel 2003

   3.    In the Solver Parameters dialog box:

  • Specify the target cell in the Set Target Cell field. In this example, the target cell is D6 - the cell that calculates the total profit for three products.
  • Because the objective is to maximize this cell, click the Max option.
  • Specify changing cells in the By Changing Cells box. In this example, changing cells are in the range B3:B5.
  • Specify the constraints which can be added one at a time and appear in the box labeled Subject to the Constraints:

To add a constraint, click the Add button. Excel displays the Add Constraint dialog box:

Add Constraint in Excel 2003

This dialog box has three parts: a Cell Reference, an operator, and a Constraint value.

To set the first constraint-that the total production capacity is 200 units - enter B6 as the Cell Reference, choose equal (=) from the drop-down list of operators, and enter 200 as the Constraint value.

Add Constraint in Excel 2003

Add another constraints:

Solver Parameters

   4.    Change the Solver options, if necessary.

   5.    Click the Solve button to start the solution process. Excel soon announces that it has found a solution and open the Solver Results dialog box:

Solver Results in Excel 2003
  • Replace the original changing cell values with the values that Solver found.
  • Restore the original changing cell values.
  • Create any or all three reports that describe what Solver did (press Shift to select multiple reports from this list). Excel creates each report on a new worksheet, with an appropriate name.
  • Click the Save Scenario button to save the solution as a scenario, so that the Scenario Manager can use it.
Results in Excel 2003

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.