Use Solver in Microsoft Excel

Optimizing values in an Excel spreadsheet to meet a given objective can be an arduous process. Fortunately, Microsoft offers Solver, a numerical optimization add-in to assist in this task. While it can’t solve every problem, It can be a very useful “what-if” tool. Here is how to use it effectively.

Steps

Before You Begin

  1. To understand the Solver, it’s important to understand the basic concept of what it does and how it works. There are 3 primary components you should be familiar with.
    • Target Cell. This is the cell that represents the goal or objective of the problem. In our example, we will be attempting to have a schedule that has no shortfall in staffing. The cell that reports any shortfall will be our Target Cell.
    • Variable Cells are the cells that can be modified to arrive at the desired outcome. In our example, that will be work hours from Monday through Friday for all employees.
    • Constraints. This are restrictions or limitations to what Solver can do to solve the problem. For example, if Employee X cannot work Tuesdays, Solver is restricted from assigning an employee work on Tuesdays.

Excel 2007

  1. Start Microsoft Excel
  2. Install the Solver add-in. If it’s not already installed, follow the next steps:
    • Click on the Tools menu and select Add-ins...
    • In the Add-ins window, enable the checkmark to the left of the Solver add-in, then click OK.
  3. Download the data used for this exercise. We will use it to create a real-world example to solve scheduling issues. When downloaded, open the file in Excel.
  4. Start Solver. Click on Tools, and then Solver.
    • The Solver Parameters window opens. This is where you enter the criteria needed to solve the given problem.
  5. Enter the Target Cell. The goal is to have no shortfall in staffing, so our target cell is B25, and we want to make it equal to the value of 0.
  6. Enter the variable cells. Click and hold on cell B5 and drag-select to cell H20.
  7. Add constraints. In this example, there are several employees who cannot work certain days of the week, and part-time employees who do not work a normal 40-hour week. Use the constraints listed below:
    • $B$11:$F$11 = 0
    • $B$12:$H$12 = 0
    • $B$22:$H$22 = $B$23:$H$23
    • $B$5:$H$20 = binary (i.e. 1's and 0's only)
    • $C$5 = 0
    • $D$18:$H$18 = 0
    • $D$8 = 0
    • $E$5 = 0
    • $F$17 = 0
    • $F$9 = 0
    • $G$14 = 0
    • $G$19:$H$19 = 0
    • $H$10 = 0
    • $I$19:$I$20 <= 24
    • $I$5:$I$18 <= 40
    • In the constraint window, enter a constraint, then click the Add button. This will add your constraint to the list, and then allow you to enter another
    • Continue entering all the constraints, and after you have entered the final one on the list, click the Cancel button return to the main Solver window.
  8. Click the Solve button. Depending on how much memory your computer has and the processor speed, Solver may take a few seconds, or a few minutes.
    • When it’s finished processing, Solver will either return a solution, or alert you that it was not able to do so.
    • If Solver is not able to find a solution, you must think through, diagnose, and debug, your problem. First, revert to your original values.
      • The two quickest fixes are to try different initial values and to add or remove constraints to the problem.
      • On the right-hand side of the Solver Results dialog box, Excel presents a series of reports. The Answer, the Sensitivity, and the Limits reports are additional sheets inserted into the current workbook. They contain diagnostic and other information and should be selected when Solver is having trouble finding a solution.
  9. When Excel reports a successful run, select Keep the Solver Solution and then click OK.

Excel 2010

  1. Changes have been made in Excel 2010 that greatly improve Solver’s functionality and interface. Use these steps to enable Solver in Excel 2010.
  2. Start Microsoft Excel
    • From the File menu, choose Options.
    • In the Excel Options dialog box, select Ad-Ins from the left sidebar, then click the Go button at the bottom of the window.
    • Click the Solver Add-in checkbox, then click OK.
  3. Access Solver. You will see it under the Data tab.
  4. Using Solver. Once you’ve installed and activated Solver, it opens a Solver Paramaters dialog box, where you will enter the parameters.

Related Articles

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Use_Solver_in_Microsoft_Excel&oldid=276504"