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

1. Press Alt-F11 to open visual basic editor
2. Press with left mouse button on Module on the Insert menu
3. Copy and paste the belowuser defined function
4. 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

Get the Excel file

Find-amounts-netting-to-zero-.xls

Find numbers in sum category

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

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

How to do tiered calculations in one formula
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]

User defined function category

Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

Extract unique distinct values in a filtered list
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

List files in a folder and subfolders [UDF]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]

Excel categories

Comments (5)