Using Solver

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

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.