I found this excel question:
I am a Controller in a multinational company. We have many transactions (sales, credits, debits, etc.) with related parties but seldom send payments. Once in a while, I would like to reduce the number of open items by identifying positive and negative amounts that net to zero and clear those items.
Currently, I dump the open items into Excel, sort by value, and manually play with items until I get a combination that is nearly zero (it seems we never find a combination that is exactly zero).
Since my goal is to minimize the number of open items, not only do I need to net as close to zero as possible (a few dollars negative or positive can be simply written off), but I also try to include as many transactions as possible in the final answer. That is, netting two transactions for +5 and -5 is fine but netting four transactions for +1, +2, +2 and -5 is better.
Here are som random numbers that will work as positive and negative amounts.
The maximum difference between possible sums is 5$ (G7). I want Excel solver to find the maximum number of positive and negative amounts that sums to almost zero. (G5).
Formula in E5:
Formula in E8:
Formula in G7:
Formula in G5:
If you already have Excel 2007 Solver add-in installed, skip next section.
Install Solver add-in
- Click Office button
- Click Excel Options
- Click add-ins
- Install Solver
- Click OK
Start Excel 2007 Solver
- Click "Data" in the Ribbon
- Click "Solver"
- Click "Set Target Cell:" button
- Click G7
- Click Max in "Equal to:".
- Click "By Changing Cells:" button and select A1:A14.
- Click "Add" button
- Click "Cell Reference:" button and select A1:A14
- Select "bin" in drop down menu (triangle)
- Click Add.
- Click "Cell Reference:" button and select G5
- Type 5 in "Constraint:" window
- Click OK!
- Click Solve.
Download excel sample file for this article.
Sum two columns.xls
(Excel 97-2003 Workbook *.xls)