Excel Solver does not obey binary constraint

Question

I created a model in Excel for use in conjunction with Solver. It basically works. The only problem is that Solver does not actually process cells that are limited as binary files. When he found a solution, some of the numbers are actually 0.9999996, 1.0000000003, 0.0000017, etc.

I am using Excel 2004 on Mac OS X. Any ideas?

Answer 1

Due to the finite precision of computers and the nature of optimization algorithms, the solver has built-in tolerances. I would try to select the Options button in the Solution Options dialog box, and then increase the accuracy (say, by default, 0.000001 to 0.00000001). I'm not sure if this will work, but worth a try.

My company makes a commercial solver connected to Excel called WhatsBest. If the above does not work, you can consider this. You can find more information at www.lindo.com

Answer 2

If the binary values ​​are far from 0 or 1, you may need to go to the options in the Solver window and check if the "Ignore whole limits" checkbox is checked.

Answer 3

If the solver cannot find a solution within a period of time or the number of iterations allowed (or if there is no correct answer), it will not return binary answers.

One possible workaround, depending on the type of problem you are trying to solve and how accurate your answer is , is to install several cells (perhaps the ones with the smallest values ​​in the cells that you calculate) with restrictions <=1 and >=0 , not binary. The image you provided is not displayed, so I'm not quite sure what you are after.

(It may be useful to set up a sumproduct cell for cells that are not set as binary, see below.)

As an example, if you are trying to solve a total of 22 (which has no solution), you start with:

 9 1 9 1 9 1 6 1 3 1 

If you set all the values ​​in the second column as binary, you will get:

 9 0.5625 9 0.5625 9 0.5625 6 0.708333333 3 0.854166667 

If you set the first 3 as binary and the last two as <=1 and >=0 , you will get:

 9 0 9 1 9 1 6 0.333333333 3 0.666666667 

The sum for the last two lines = 4, which you could use to manually set one of the last two as 1 and the other 0, depending on whether you want to get an answer just below or above the target.

Answer 4

I had a similar problem and I was able to solve it by going to the solver options and then checking "Use automatic scaling". I'm not sure if this will fix your problem or not, but it's worth a try.

Answer 5

This is probably not your problem, but keep in mind that there is an Ignore Integer Constraints checkbox in the settings, which is checked by default in Excel Solver. At least this case in my copy of Excel 2011 for Mac.