Finding combinations to equal the total
Posted by Paul Balzano on June 06, 2001 2:27 PM
How can I get Excel to find the combinations of check amounts that equal the Total amount? We receive a total dollar amount from the bank (the known). I have hundreds of check amounts in a range. Some combination if added together will equal the total. There could be 1 check to n checks that could add up to the total. Not all checks are cashed at the same time and there are always check amounts in the range. We are only interested in the combination of check amounts equaling the Total amount. And moving the identified check amounts used out of the range after they become part of the total amount.
Posted by Mark W. on June 06, 2001 2:53 PM
What if...
{"Check Nbr","Amount"
;101,12.01
;102,7.72
;103,6.16
;104,10.45}
Check Nbr sets {101,103} and {102,104} both equal
$18.17. What are you gonna do?
Posted by Paul Balzano on June 07, 2001 7:24 AM
Re: What if...
At the time the Total Amount is received from the bank, we do not know which check numbers are included in the Total Amount. We are working with running totals and therefore the exact check numbers are only needed at the end of the month. We are checking account balances and need to know what amounts were paid.
Posted by Mark W. on June 07, 2001 11:52 AM
Solver
I constructed a Solver model that seems to work
fairly well although sometimes it "thinks" it hasn't
found a solution when clearly it has. Here's what
I did with a beginning data set in cells A1:B8...
{"Check Nbr","Amount"
;101,5.54
;102,7.72
;103,6.16
;104,10.45
;105,9.33
;106,8.83
;107,6.47}
1. Enter the formula, =SUMPRODUCT(B2:B8,C2:C8),
into cell B10.
2. Enter the value, 18.17, into cell A12 which
will serve as your Total amount input.
3. Enter the formula, =A12*B12, into cell C12.
4. (Optional) Format cell B12 as: "x " 0 " ="
5. Choose the Tools | Solver... menu command.
6. Enter $B$10 as the "Set Target Cell" and click
the "Max" radio button.
7. Enter $C$2:$C$8,$B$12 as the "...Changing Cells"
8. Add the following constraints:
(1) $B$10 = $C$12
(2) $B$10 >= 1
(3) $B$12 = integer
(4) $C$2:$C$8 = binary
9. Press the [ Solve ] button.
10. The check numbers will be "flagged" with
a 1 in column C, and the value in cell B12 (2)
indicates the number of qualified sets. In
this case... {101;103;107} and {102;104}, but
unfortunately you'll have to eyeball the list
to figure out the set composition. At least
this easier than scanning a lengthy list! : )
Posted by Paul Balzano on June 11, 2001 5:52 AM
Re: Solver
Mark --- Thank you for your help! It does work well with 9 enteries or less, but selects all from 10 on up. We really appreciate you bring us new insight on using Solver and you have given us some ideas of our own. Thanks Again
