Excel Tip of the Week #257 - Revisiting the Solver Add-In

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a Developer-level post and we're taking a fresh look at the advanced Goal Seek add-in, Solver.  Solver was first covered in TOTW #116, and we revisited the simpler Goal Seek a few weeks ago in TOTW #250.

What is Solver and how do I install it?

Solver is an add-in that can find optimal solutions for problems described in Excel.  It can quickly automate the process of trial-and-error to find solutions for complex problems with multiple constraints and multiple inputs.  It is a powerful tool for skipping grunt work.

It's available for free and comes bundled with all Windows versions of Excel.  To activate it:

  1. Navigate to Excel Options => Add-Ins
  2. At the bottom of that menu, select Manage Add-Ins => Go
  3. Tick the Solver Add-In
  4. OK

Solver should then appear in the Data Ribbon.

How do I set up a scenario in Solver?

The most important thing is to carefully create your scenario and think it through so that you know what all the constraints you have to satisfy are, and they are all easy to express.

Here are some example constraints:

  • A resource that can't be overspent - constraint is a cell calculating the remaining amount that must remain >=0
  • An option that can be enabled or disabled - a cell which must have a value of either 0 or 1
  • A trigger condition that must be met - a cell that is equal to 1 when the condition is met
  • Numbers which represent whole numbers, such as the number of staff assigned to a task - cells which must have integer values
  • Ordering of a series of operations or processes - cells which represent the ordering which must be different, must be integers, and must be in the range expected

All of these are easy to transfer into Solver but must be carefully written beforehand.

Solver can find the maximum or minimum outcome for a given problem, or can find a set of inputs that lead to a given target outcome.

Once your scenario is all set up, we can then start to input it into Solver.

Using Solver

Above is the main Solver menu.  Working from top to bottom:

Set Objective defines the single cell that will be monitored

To determines the goal that Solver will work towards

By changing variable cells defines which cell or range of cells Solver is allowed to manipulate in order to find its goal

Constraints allows you to set in any number of constraints - either constraints to the variable cells, or constraints elsewhere that the solution must satisfy

Constraints can apply to a cell or range of cells, and can refer either to a fixed constraint value or refer to a cell for a calculated or variable one.  As well as constraints of being greater / equal / lesser to a value, you can also use the final three constraints: int (Integer, must be a whole number), bin (Binary, must be either 0 or 1), and dif (Different, must all be different).

Solving method and the associated Options menu lets you pick from the different algorithms that Solver might use - see the description shown in the box for guidance on how to pick one, but in most circumstances the method chosen won't matter too much.

When that's all done, hit Solve!

The outcome of running Solver

Solver can take a while to run depending on the complexity of your problem, so try to have a minimum of other files and programs running alongside it.  Eventually, you will probably see the above dialog box show up.  The values of your variable cells will have been changed to Solver's solution, and Solver will tell you how it got on.  You can leave the values as Solver has suggested or go back and try other values.  You can even save the outcome as a Scenario that you can easily return to later from the Scenario Manager.

Do remember that Solver isn't magic, and may not find a solution even if one exists - it is set to give up after a while rather than run forever.  And your problem may not have a solution, for that matter!

Solver is something best tried out in practice, so open the file attached at the bottom of this post and give one of the practice scenarios a try.

Previous post - Power Query - Three-way matching
TOTW index 
Next post - A guide to common Excel file types

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Anonymous