Find positive and negative amounts that net to zero
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:
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to [โฆ]
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:
Formula in E8:
Formula in G7:
Formula in G5:
If you already have Excel 2007 Solver addin installed, skip next section.
Install Solver addin
 Click Office button
 Click Excel Options
 Click addins
 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 972003 Workbook *.xls)
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me [โฆ]
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones [โฆ]
How to enter array formulas in merged cells
Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: [โฆ]
Sum cells containing numbers and text based on a condition
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and [โฆ]
Identify numbers in sum using Excel solver
Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from [โฆ]
Optimize pick path in a warehouse
As you probably already are aware of I have shown you earlier a vba macro I made that finds the [โฆ]
Using Excel Solver to schedule employees
This is a question I found at the bottom of this page Using Solver to schedule your workforce Bank 24 [โฆ]
Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back [โฆ]
6 Responses to โFind positive and negative amounts that net to zeroโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Above example is very useful. But i want to know how to get a combination that nets exactly to zero and not close to zero.
MITH,
I think this page can be helpful: https://www.tusharmehta.com/excel/templates/match_values/index.html
VBA code to find multiple matching combinations
Hi Hope all are good.
I have upgraded to Excel 2010. The file I was working on earlier version now gives different result. The Solver also looks very different. Can any body help me understand the new solver.
if there more rows like 100 row .. it is not working
It's not working in excel 2010
R D Veeramani
I do believe it works in Excel 2010, can you explain in greater detail what error message you get?