This is probably too late to help you if you haven't already solved your
problem...
You are on the right track. The approach Solver uses is a 'standard' one
for problems with binary variables. Unfortunately, the default version of
Solver has some limits on the size of the problem it can solve.
For a Solver based template or for VBA code that lists all (or as many as
desired) combinations, see
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/te...ues/index.html
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article , denton120
@yahoo.com says...
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=3F=3Ft 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 =3F=3Fswitches=3F. 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?