# 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:

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):

** 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:

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:

** 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:

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

Permission is granted for article quotations from OfficeToolTips, provided the direct link to the source article

http://www.officetooltips.com/excel 2016/tips/using solver.html