Using Solver

Excel 2016
The 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 the sales of three products. Column B consists of 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 365

To maximize total profit, the 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 find it, see How to manage Add-Ins):

The Analysis in Excel 365

   3.    In the Solver Parameters dialog box:

Solver Parameters in Excel 365
  • Specify the target cell in the Set Objective 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 Variable 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 365

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

    All Constraints in Excel 365

   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 365
  • 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 Outline Reports checkbox.

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

See also this tip in French: Utiliser le Solveur.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.