Find positive and negative amounts that net to zero [UDF]
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
This article describes a UDF (custom function) that finds positive and negative numbers that net to approximately zero. The UDF lets you specify the range to use around 0 (zero).
I created/modified an UDF to solve his question:Â Find positive and negative amounts that net to zero in excel
Array formula in cell range c23:V55:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
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 add the User defined Function to your workbook
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste the belowuser defined function
- Exit visual basic editor
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
Find numbers in sum category
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 […]
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]
User defined function category
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
Excel categories
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
Paste image link to your comment.
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, pressed with left mouse button on 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.