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 of Solver in Excel 2016

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.    On the Data tab, in the Analysis group, click Solver... (Solver is an add-in, if you can't found it, see How to manage Add-Ins):

Analysis in Excel 2016

    3.    In the Solver Parameters dialog box:

Solver Parameters in Excel 2016
  • 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 2016

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 another constraints:

All Constraints in Excel 2016

    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 2016
  • 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.

    To see reports, check the Outlone Reports checkbox.

  • Click the Save Scenario button to save the solution as a scenario, so that the Scenario Manager can use it.
Results in Excel 2016