You set your Excel Solver model past using the Solver Parameters dialog box. Yous use the Fix Objective box to specify the objective jail cell, and you use the To grouping to tell Excel Solver what you lot want from the objective cell: the maximum possible value; the minimum possible value; or a specific value. Finally, you use the By Changing Variable Cells box to specify the cells that Solver can use to plug in values to optimize the result.

Optimizing Excel Solver results

When Solver finds a solution, you tin choose either Keep Solver Solution or Restore Original Values. If you lot choose Go on Solver Solution, Excel permanently changes the worksheet. Yous cannot undo the changes.

With your Solver-set worksheet model fix to get, here are the steps to follow to discover an optimal result for your model using Solver:

  1. Choose Data → Solver.

    Excel opens the Solver Parameters dialog box.

  2. In the Set Objective box, enter the address of your model's objective cell.

    Note that if you click the cell to enter information technology, Solver automatically enters an absolute jail cell address (for example, $B$14 instead of B14). Solver works fine either way.

  3. In the To group, select an pick:
    • Max: Returns the maximum possible value.
    • Min: Returns the minimum possible value.
    • Value Of: Enter a number to set the objective prison cell to that number.

      For the example model, Value Of has been selected and 0 entered in the text box.

  4. In the Past Irresolute Variable Cells box, enter the addresses of the cells you lot desire Solver to change while information technology looks for a solution.

    In the instance, the changing cells are B4 and C4. The post-obit prototype shows the completed Solver Parameters dialog box.

    Solver Parameters Excel The completed Solver Parameters dialog box.

  5. Click Solve.

    Solver gets downwardly to concern. As Solver works on the problem, you lot might see the Show Trial Solution dialog boxes bear witness up one or more times.

  6. In any Show Trial Solution dialog box that appears, click Continue to move things forth.

    When the optimization is complete, Excel displays the Solver Results dialog box.

    Excel Solver Results The Solver Results dialog box and the solution to the break-fifty-fifty problem.

  7. Select the Keep Solver Solution option.

    If you don't desire to accept the consequence, select the Restore Original Values option instead.

  8. Click OK.

Y'all tin can ask Solver to display 1 or more reports that requite you extra information well-nigh the results. In the Solver Results dialog box, utilize the Reports listing to select each written report you want to view:

  • Reply: Displays information about the model's objective cell, variable cells, and constraints. For the objective cell and variable cells, Solver shows the original and final values.
  • Sensitivity: Attempts to show how sensitive a solution is to changes in the model's formulas. The layout of the Sensitivity study depends on the blazon of model you're using.
  • Limits: Displays the objective jail cell and its value, equally well as the variable cells and their addresses, names, and values.

Excel Solver tin use one of several solving methods. In the Solver Parameters dialog box, use the Select a Solving Method listing to select one of the post-obit:

  • Simplex LP: Use if your worksheet model is linear. In the simplest possible terms, a linear model is one in which the variables are not raised to whatsoever powers and none of the so-called transcendent functions — such as SIN and COS — are used.
  • GRG Nonlinear: Apply if your worksheet model is nonlinear and smooth. In general terms, a smooth model is one in which a graph of the equation used doesn't show sharp edges or breaks.
  • Evolutionary: Use if your worksheet model is nonlinear and nonsmooth.
Exercise you lot accept to worry about any of this? Almost certainly non. Excel Solver defaults to using GRG Nonlinear, and that should work for well-nigh annihilation you do with Solver.

Adding constraints to Excel Solver

The real world puts restrictions and weather on formulas. A factory might have a maximum chapters of 10,000 units a day, the number of employees in a visitor tin't be a negative number, and your advertizing costs might be restricted to ten percent of total expenses.

Similarly, suppose that y'all're running a suspension-even analysis on ii products. If you run the optimization without any restrictions, Solver might reach a total profit of 0 by setting i product at a slight loss and the other at a slight profit, where the loss and profit cancel each other out. In fact, if y'all take a close wait at the previous image, this is exactly what Solver did. To become a true pause-even solution, you lot might prefer to see both product profit values as 0.

Such restrictions and weather condition are examples of what Solver calls constraints. Adding constraints tells Solver to find a solution so that these conditions are not violated.

Hither's how to run Solver with constraints added to the optimization:

  1. Choose Data → Solver.

    Excel opens the Solver Parameters dialog box.

  2. Use the Set up Objective box, the To group, and the Past Changing Variable Cells box to set up upwardly Solver as described above.
  3. Click Add.

    Excel displays the Add Constraint dialog box.

  4. In the Cell Reference box, enter the address of the jail cell y'all desire to constrain.

    You can blazon the address or select the cell on the worksheet.

  5. In the drop-downward list, select the operator you want to use.

    Nigh of the time, you use a comparison operator, such as equal to (=) or greater than (>). Utilize the int (integer) operator when you need a constraint, such as full employees, to be an integer value instead of a real number (that is, a number with a decimal component; you can't have ten.5 employees!). Use the bin (binary) operator when you accept a constraint that must be either TRUE or FALSE (or 1 or 0).

  6. If you chose a comparison operator in Step five, in the Constraint box, enter the value by which you lot desire to restrict the cell.

    This paradigm shows an instance of a completed Add Constraint dialog box. In the example model, this constraint tells Solver to find a solution such that the product profit of the Inflatable Dartboard (cell B12) is equal to 0.

    Add Constraint dialog Excel The completed Add Constraint dialog box.

  7. To specify more than constraints, click Add and echo Steps 4 through vi, equally needed.

    For the example, y'all add a constraint that asks for the Dog Polisher production profit (cell C12) to be 0.

  8. Click OK.

    Excel returns to the Solver Parameters dialog box and displays your constraints in the Bailiwick to the Constraints list box.

  9. Click Solve.
  10. In any Prove Trial Solution dialog box that appears, click Go on to motion things along.

    The image beneath shows the instance break-even solution with the constraints added. Notice that non only is the Total Profit jail cell (B14) set up to 0, but so are the two Product Turn a profit cells (B12 And C12).

    Excel Solver results The Solver Results dialog box and the final solution to the pause-even problem.

  11. Select the Keep Solver Solution option.

    If y'all don't want to accept the result, select the Restore Original Values selection instead.

  12. Click OK.

You can add a maximum of 100 constraints. Likewise, if y'all demand to make a alter to a constraint before you begin solving, select the constraint in the Bailiwick to the Constraints list box, click Modify, and then make your adjustments in the Change Constraint dialog box that appears. If y'all want to delete a constraint that you no longer demand, select the constraint and and so click Delete.

Save an Excel Solver solution equally a scenario

Whenever you have a spreadsheet model that uses a coherent ready of input values — known as changing cells — you take what Excel calls a scenario. With Solver, these changing cells are its variable cells, so a Solver solution amounts to a kind of scenario in Excel. However, Solver does not requite you an like shooting fish in a barrel way to save and rerun a particular solution. To work effectually this trouble, you tin relieve a solution as a scenario that you tin then later recall using Excel'due south Scenario Manager feature.

Follow these steps to save a Solver solution as a scenario:

  1. Cull Data → Solver.

    Excel opens the Solver Parameters dialog box.

  2. Apply the Set Objective box, the To grouping, the By Changing Variable Cells box, and the Subject to the Constraints list to ready Solver as described above.
  3. Click Solve.
  4. Anytime the Testify Trial Solution dialog box appears, choose Continue.

    When the optimization is complete, Excel displays the Solver Results dialog box.

  5. Click Save Scenario.

    Excel displays the Relieve Scenario dialog box.

  6. In the Scenario Name dialog box, type a name for the scenario and so click OK.

    Excel returns you to the Solver Results dialog box.

  7. Select the Go along Solver Solution option.

    If you don't want to take the upshot, select the Restore Original Values choice instead.

  8. Click OK.

Nearly This Article

About the book author:

Paul McFedries is a true renaissance geek. He has been a programmer, consultant, database programmer, and website builder. He's as well the author of more than 90 books, including top sellers covering Windows, Function, and macOS.

This article can be institute in the category:

  • Excel ,