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.

Answer:

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:

=SUMPRODUCT(A1:A7;B1:B7) + ENTER

Formula in E8:

=SUMPRODUCT(A8:A14;B8:B14) + ENTER

Formula in G7:

=SUM(A1:A7)+SUM(A8:A14) + ENTER

Formula in G5:

=ABS(E5+E8) + ENTER

If you already have Excel 2007 Solver add-in installed, skip next section.

Install Solver add-in

1. Click Office button
2. Click Excel Options
3. Click add-ins
4. Install Solver
5. Click OK

Start Excel 2007 Solver

1. Click "Data" in the Ribbon
2. Click "Solver"
3. Click "Set Target Cell:" button
4. Click G7
5. Click Max in "Equal to:".
6. Click "By Changing Cells:" button and select A1:A14.
7. Click "Add" button
   
8. Click "Cell Reference:" button and select A1:A14
9. Select "bin" in drop down menu (triangle)
10. Click Add.
11. Click "Cell Reference:" button and select G5
12. Type 5 in "Constraint:" window
13. Click OK!
14. Click Solve.

Download excel sample file for this article.
Sum two columns.xls
(Excel 97-2003 Workbook *.xls)

Related posts:

1. Extract negative values and adjacent cells in excel
2. Identify numbers in sum using solver in excel
3. Using Excel Solver to schedule employees
4. Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel
5. Extract a unique distinct list by matching items that meet a criterion in excel
6. Visualize date ranges in a calendar in excel
7. Create dependent drop down lists containing unique distinct values in excel