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:
Recommended articles
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 add-in installed, skip next section.
Install Solver add-in
- Press with left mouse button on Office button
- Press with left mouse button on Excel Options
- Press with left mouse button on add-ins
- Install Solver
- Press with left mouse button on OK
Start Excel 2007 Solver
- Press with left mouse button on "Data" in the Ribbon
- Press with left mouse button on "Solver"
- Press with left mouse button on "Set Target Cell:" button
- Press with left mouse button on G7
- Press with left mouse button on Max in "Equal to:".
- Press with left mouse button on "By Changing Cells:" button and select A1:A14.
- Press with left mouse button on "Add" button
- Press with left mouse button on "Cell Reference:" button and select A1:A14
- Select "bin" in drop down menu (triangle)
- Press with left mouse button on Add.
- Press with left mouse button on "Cell Reference:" button and select G5
- Type 5 in "Constraint:" window
- Press with left mouse button on OK!
- Press with left mouse button on Solve.
Get excel sample file for this article.
Sum two columns.xls
(Excel 97-2003 Workbook *.xls)
Misc category
I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: […]
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
Column B contains document names, many of them are duplicates. The adjacent column C has the revision of the documents […]
This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]
Solver category
Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]
As you probably already are aware of I have shown you earlier a vba macro I made that finds the […]
The COMBIN function returns the number of combinations for a specific number of elements out of a larger number of […]
This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]
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 […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
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.
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.tushar-mehta.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?