Author: Oscar Cronquist Article last updated on December 11, 2018

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:

The following article solves this question using an array formula:

Find numbers closest to sum

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Find numbers closest to sum

The answer here uses the Excel Solver, here are some 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. Press with left mouse button on Office button
  2. Press with left mouse button on Excel Options
  3. Press with left mouse button on add-ins
  4. Install Solver
  5. Press with left mouse button on OK

Start Excel 2007 Solver

  1. Press with left mouse button on "Data" in the Ribbon
  2. Press with left mouse button on "Solver"

  3. Press with left mouse button on "Set Target Cell:" button
  4. Press with left mouse button on G7
  5. Press with left mouse button on Max in "Equal to:".
  6. Press with left mouse button on "By Changing Cells:" button and select A1:A14.
  7. Press with left mouse button on "Add" button

  8. Press with left mouse button on "Cell Reference:" button and select A1:A14
  9. Select "bin" in drop down menu (triangle)
  10. Press with left mouse button on Add.
  11. Press with left mouse button on "Cell Reference:" button and select G5
  12. Type 5 in "Constraint:" window
  13. Press with left mouse button on OK!
  14. Press with left mouse button on Solve.


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