Solver Binary Contraints problem

[Rick Kaullen]

I'm using Solver to determine which combination of numbers in a column
result in the given total.

For example, the data is in E1:E42 and I've set J1:J42 to 0.
$J$43 = SUMPRODUCT(E1:E2*J1:J42)
$J$44 = the value I'm searching for
$J$45 = J44-J43 and is the Solver Target Cell
I'm Solving to 0 by changing cells $J$1:$J$42
Subject to the Constraints: $J$1:$J$42=binary

When I set the constraint to binary, instead of setting the changeable
cell from 0 to 1, it sets the cells to a large decimal between 0 and
1.
I've also tried adding the integer constrait to no avail.
Is there any way to force Solver to limit the values of the changeable
cells to either a 0 or 1?
Thanks.

 

[Tushar Mehta]

The technique that Solver uses (Branch and Bound) is a standard one from the
field of optimization. Essentially, it uses real numbers (i.e., ignores the
binary/integer constraint) except at certain points in the process when it
brings them back into play.

So, if your optimization fails before completion (no feasible solution,
exceeds allowed iterations / time, etc.), some of the integer values may be
non-integer.

Also, Solver allows one to control the accuracy of the final solution
through 3 variables. In the Solver dialog box, click the Options button.
In the next dialog box, click the Help button and read up on Precision,
Tolerance, and Convergence.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions