## 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 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)

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 […]

Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back […]

How to solve simultaneous linear equations in Excel

This article demonstrates how to solve simultaneous linear equations using formulas and Solver. The variables have the same value in […]

### 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.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?