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
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:
=FIND_num(B2:B21, E21, 5) + CTRL + SHIFT + ENTER.
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 FunctionRelated posts:
Find positive and negative amounts that net to zero in excel
Excel udf: Find numbers in sum
Recommended categories
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.