Excel > Excel 2003 Advanced > What-If Analysis


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.

  1. Click Tools > Add-Ins
  2. The Add-Ins dialogue box is displayed, showing a list of available Excel add-ins.

  3. Click the Solver Add-in check box to select it
  4. Adding Solver
  5. Click Ok

The Solver tool is now available from the Tools menu

Using Solver

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

Solver data

  1. Click Tools > Solver
  2. The Solver Parameters dialogue box appears

    Solver parameters

  3. In the Set Target Cell field, select the cell where the maximum revenue will appear
  4. 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
  5. Enter the cells whose value must change to produce the result in the By Changing Cells field
  6. Click the Add button to add the constraints
  7. Adding constraints

    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

    Completed solver parameters

  8. Click Solve
  9. The Solver Results dialogue box is displayed.

    Solver results

  10. Choose to either Keep Solver Solution or Restore Original Values and click Ok
  11. 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.

Follow us on

Facebook  Twitter  You Tube