Hi. A Binary Constraint that ends up being 0.3065 usually indicates
that Solver aborted early because it got confused, and gave up.
For this type of problem, the usually technique is to set B1:B6 as
binary constraints as you did.

The third column is usually not used. The Sum formula is
=Sumproduct (A1:A6, B1:B6)

The Target Cell is as you have done.

The Target Cell will almost never be Zero.
So, one usually minimizes the Target Cell with the constraint that
Target Cell = -.005 (or something similar close to zero depending on
what you are doing)

You should also adjust Solver's options for Precision, Tolerance, and
Convergence.

HTH
Dana DeLouis


On Wed, 28 Mar 2007 17:08:05 -0700, denton
wrote:

I have a general ledger account that is out of a balance. Auditors tomorrow
will force a large write off or other fines if this account is not
reconciled. In order to find where the account is off I am trying to solve
mathematically where it is right. Large wire transfers (credits) are made of
many debits but I don’t know which debits are making up the wire transfers.
In other words, I have a number X and some grouping of other numbers equals
X.

I set up an excel solver model that that attempts to test every combination
by using binary “switches”. The numbers 0 or 1 multiply by each test value
to give either 0 or the value itself. All of these result values are added
up and if the resulting sum matches number X then we found are match. The
problem is the Excel solver model is not really solving using a binary
method. It is testing all values from 0 to 1 and telling me if I get a
solution that meets the binary constraints.

Here is small sample data table and the constraints:
Changing cells: B2:B6
Constraint: Difference = 0
Constraint: B2:B6 = binary

Test Values Binary # Result
$456,136.59 1 $456,136.59
$456,136.59 1 $456,136.59
$197,181.09 0 $0.00
$195,487.11 1 $195,487.11
$521,298.96 0 $0.00
$130,324.74 0.0002 $31,767.42
$65,162.37 0.25047 $16,321.22
$390,974.22 0.30657 $119,862.06
$252,198.01 0.231078 $58,277.46

Sum of Results: $1,333,988.45

Number X: 3,359,333

Difference (Target of zero): ($2,025,344.55)


What is the fastest way to setup a solution in Excel or custom programming
(I can program C#,VBA) to solve this problem?