## Find positive and negative amounts that net to zero

*Article last updated on February 24, 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:**

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:

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)

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

Convert array formula to a regular formula

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

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

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

Use the img tag, like this: <img src="Insert pic link here">

**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

[...] Excel udf: Find positive and negative amounts that net to zero Filed in Excel, Finance, User defined functions (udf) on Feb.25, 2011. Email This article to a Friend I created/modified an udf to solve his question: Find positive and negative amounts that net to zero in excel [...]

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.