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