## Find positive and negative amounts that net to zero [UDF]

*Article last updated on February 24, 2018*

This article describes a UDF that finds positive and negative numbers that net to approximately zero. You specify the range around 0 (zero).

I created/modified an udf to solve his question: Find positive and negative amounts that net to zero in excel

**Question**:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero and clear those items

Criteria:

- Minimze the number of open items
- include as many transactions as possible
- net as close to zero as possible

**Udf (array) in cell range c23:V55:**

The udf processes these 20 numbers in around 30 seconds. There are 18 numbers found in row 24 and also in row 25 and 31.

Formula in column W sums values in each particular row.

Formula in column X counts values.

**How to use user defined function in excel**

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste the belowuser defined function
- Exit visual basic editor

### Download excel sample file for this tutorial.

Find amounts netting to zero.xls

(Excel 97-2003 Workbook *.xls)

VBA code

Function Find_num(rng As Range, cell As Range, num_range As Single) Dim com() As Single, c As Single, i As Single Dim s As Single, d As Single, u As Single, v As Single Dim arr() As String, r As Single, p As Single, t As Single Dim sum_cells As Single, j As Single, k As Single, l As Single sum_cells = Application.WorksheetFunction.Sum(rng) c = rng.Rows.Count ReDim arr(1 To 10000, 1 To c) r = 1 For i = 0 To Int(c / 2) If i <> Int(c / 2) - 1 Then t = WorksheetFunction.Combin(c, i + 1) Else t = WorksheetFunction.Combin(c, i + 1) / 2 End If ReDim com(i) For j = 0 To i com(j) = j + 1 Next k = i For s = 1 To t If com(k) > c And i > 0 Then p = 0 Do Until com(k) <= c - p com(k - 1) = com(k - 1) + 1 k = k - 1 p = p + 1 Loop Do Until k >= i k = k + 1 com(k) = com(k - 1) + 1 Loop End If d = 0 For j = 0 To i d = d + rng(com(j)) Next j If d <= cell + num_range And d >= cell - num_range Then For j = 1 To i + 1 arr(r, j) = rng(com(j - 1)) Next j r = r + 1 End If If sum_cells - d <= cell + num_range And sum_cells - d >= cell - num_range Then For j = 1 To c v = 0 For u = 0 To i If rng(com(u)) = rng(j) Then v = 1 Next u If v = 0 Then arr(r, j) = rng(j) End If Next j r = r + 1 End If com(k) = com(k) + 1 Next s Next i Find_num = arr() End Function

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

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

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

List files in a folder and subfolders [UDF]

This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Filter unique distinct words from a cell range [UDF]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

### 5 Responses to “Find positive and negative amounts that net to zero [UDF]”

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

Thanks for covering this topic (I've been looking for something like this for six months and had just about given up hope). Unfortunately, I'm having implementing it. Am I just supposed to have my amounts entered in column B, and then only to follow the "How to use user defined function in Excel", or do I have to also enter the formulas also. If so, in what cell do I enter =FIND_num(B2:B21, E21,5) + CTRL + SHIFT + ENTER ? Do I also need to enter any formulas in any other cells (if so, what do I enter and which cell(s) do I enter it. Thanks!

Mike,

You enter the =FIND_num(B2:B21, E21,5) in your cell range.

Example

1. Select your cell range (C23:V55 in the example above)

2. Paste user defined function the in formula bar

3. Press and hold Ctrl + Shift

4. Press Enter

=Find_num(cellrange, sum, range)

See the explaining picture above.

Thanks for the quick response, Oscar. I pressed alt-F11 to open vba editor, clicked Module on the Insert menu, copy and pasted the udf, exited vba editor, selected cell range of C23:V55, pasted the udf function in the formla bar, pressed and held Ctrl + Shift, and pressed enter. The function ran (which was very exciting!) and the results showed in columns C24 thru V55, but there weren't any results in column W or X. Please advise. Thanks.

Mike,

I added formulas in colummns W and X to show that the sum is in the specified range (-5 to 5) and how many numbers were found.

=FIND_num(B2:B21, E21, 5)

B2:B21 - Numbers

Cell E21 contains the sum value

5 is the range

Hello Oscar,

it is great tool you presented. I need to search inbetween of plenty items (1000 and more lines) I tried to exted formulas and matrix in file however when I try with more than 25 items I receive #VALUE error as an example. Do you have any idea what that is?

Thank you in advance for help.