Solver is a tool that can solve complex problems using multiple variables. Solver can also handle constraints that need to be met.
Install the Solver add-in
Solver is an Excel add-in. This needs to be installed before you can use Solver.
- Click the Office Button and then click the Excel Options button
- Click Add-Ins from the list on the left
- If necessary, click the Manage list arrow and select Excel Add-ins
- Click the Go button
- Click the Solver Add-in check box to select it
- Click Ok
The Add-Ins dialogue box is displayed, showing a list of available Excel add-ins.
The Solver tool is now available in the Analysis group of the Data tab on the Ribbon
Let's use Solver to find out the maximum revenue we can make in one evening for a theatre production based on the tickets prices and some constraints
Download Solver.xlsx to follow the example in this tutorial
For Solver to present a solution it needs certain items of information from us..
- Target Cell: The cell where the result will appear. This cell must contain a formula.
- Changing Cells: The cells containing the variable values that Solver will update whilst trying to calculate the result .
- Constraints cells: Cells containing values used in conditions that need to be met
Before running Solver, ensure that all the data needed is on the sheet. This includes the formula in the target cell, cells for changing cells and the constraints.
The data used in the following example can be seen below
- Click on the Solver button in the Analysis group of the Data tab
- In the Set Target Cell field, select the cell where the maximum revenue will appear
- Select an Equal To option button for the target you wish to achieve. You can specify a specific value in the Value of field, or choose to find the maximum or minimum value as used in this example
- Enter the cells whose value must change to produce the result in the By Changing Cells field
- Click the Add button to add the constraints
- Click Solve
- Choose to either Keep Solver Solution or Restore Original Values and click Ok
The Solver Parameters dialogue box appears
Enter the condition to be met and click the Add button to add more constraints, or click Ok to return to the Solver Parameters dialogue box
The Solver Results dialogue box is displayed.
Solutions can be saved as scenarios by clicking the Save Scenario button in the Solver Results dialogue box. Saving solutions as scenarios means that you can quickly review several targets without having to recreate the solver each time.