Solver
Add-In
The Solver optimization add-in that ships with Excel is used extensively in our books. Solver is not a Microsoft product. It was developed by Frontline Systems, which has developed a number of Solver products, some much more powerful than the version of Solver that comes with Excel. (See solver.com for more information.)
We have no control over the idiosyncrasies of Solver – and there are some – but I’ll discuss Solver issues on this page as I hear of them.
·
Bug
fix in Solver for Excel 2010 and later. There are certain
models where Solver for Excel 2010 or later versions can give the wrong solution.
The conditions are that (1) you are maximizing, (2) the starting solution is
feasible, and (3) you have integer constraints. Frontline Systems has fixed the
problem, and you can download the fix: For the 32-bit: Solver Fix 32-bit.zip, and 64-bit: Solver Fix 64-bit.zip. Each
of these zips contains a single file called Solver32.dll, but the two
Solver32.dll files aren’t the same. One is for 32-bit Excel and the other is
for 64-bit Excel, so make sure you download the right one. Once you download
the appropriate file, you should save its dll file to the appropriate folder on
your hard drive, overwriting the previous dll. This folder will typically be something
like C:\Program Files\Microsoft Office\Officexx\Library\Solver, although you
might have to search for the dll on your PC.
·
Model
not linear. Do you occasionally get the annoying
Solver message that the conditions for a linear model are not satisfied, when
you know your model is linear? I do! Frontline Systems has suggested
that this is typically due to a poorly-scaled model (some really small numbers
mixed with some really large numbers). Here are three possible remedies.
o
Rescale the model manually. For
example, if unit costs are shown as millions of dollars, as in 5,000,000,
replace them with 5.
o
Under Solver Options, check the Use
Automatic Scaling option. Solver will do its best to rescale appropriately,
maybe successfully, maybe not.
o
Under Solver Options, change the
precision to a larger number (i.e., fewer zeros, such as 0.00001 instead of
0.000001). Essentially, this relaxes Solver's criterion for what is linear and
what isn't (see next bullet), so that truly linear models will more likely be
recognized as such.
·
Test
for linearity. The previous bullet indicates what to
do if you’re sure your model is linear but Solver claims otherwise. But how
does Solver make its check for linearity in the first place? Here’s the idea.
Let’s say your function, written algebraically, is f(x,y) = 4x+6y, where x and
y are decision variables. This is exactly what we mean by a linear function: a
sum of products of constants and decision variables. To check that this
function is linear, Solver approximates the derivatives of the function
numerically at various points. For example, the approximate derivative with
respect to x at the point (x,y) is [f(x+dx,y)-f(x,y)]/ dx, for some small
value dx.
For our function, this is [(4(x+dx) + 6y) – (4x+6y)]/ dx = 4dx/dx
= 4. This answer should clearly be 4 regardless of the values of x and y. If it
is “sufficiently close” to 4 for a number of points (x,y), i.e., if the
derivative is practically constant from point to point, then Solver concludes
that the function is linear. However, numerical computer error can make this
value slightly different from 4, and this error is typically larger when a
model is poorly scaled (some really small numbers mixed with some really large
numbers). If the error is large enough, i.e., if the derivatives don’t seem to
be constant from point to point, Solver reports that the model isn’t linear.
·
Using Solver with VBA: You can invoke
Solver's functionality with a VBA macro. In fact, I did this in a number of the
applications in my VBA for Modelers
book. To do this, you must first set a "reference" to the Solver.xlam
file (or the Solver.xla file for old versions of Excel) in the VB editor.
However, depending on how Solver is setup on your computer, when you run one of
these applications, you might get a message about "Missing Solver
reference" or some such thing. This has been a recurring problem with
users, so I finally asked my contact at Frontline Systems for a fix. Here is
his response: When a workbook has a reference to an add-in, Excel will store
this reference with its entire path. Now, when a workbook gets opened in Excel,
and it has a reference to an add-in, which has not been opened yet, Excel will
try to open the add-in, from the path that was stored in the reference. If this
is not successful (for example, the workbook was saved on another machine with
a different file location) Excel will add the "Missing: solver.xlam"
reference. To fix this, you would have to open solver.xlam (or solver.xla) by
invoking Solver from Excel, before opening your workbook. Then open your
workbook and save the workbook.
Visit the Cengage site for our books.
Send e-mail to albright@indiana.edu
Albright and Winston are both
retired from the Kelley
School of Business, Indiana University, Bloomington.
Back to home
page
Updated: 3/14/2016