Finding combinations of cells that sum to near a value

[LauraB]

Starting with a column of ~30 numbers, I'm looking for a way to have
Excel find all combinations that sum to a target value +/- a specified
range width. (For example, I may be looking for all combinations that
sum to 1000 +/- 10.)

I've found some code from Tushar Mehta that finds a list of all
possible matches that sum to a number, but it doesn't allow me to put
in a range to find sums to within a delta of this value. I tried
modifying the code to add this in, but I couldn't get it to work
right. Here is the code: http://www.tushar-mehta.com/excel/templates/match_values/index.html

Does anyone have any suggestions?

Thank you,
Laura

 

[LauraB]

Clarification: I did get the code to almost work, but when I put in a
range (labeled Diff in the code) it no longer returns all possible
combinations, only some of them. Here's the code:


Sub Combinations()
Option Explicit
Function RealEqual(A, B, C)
RealEqual = Abs(A - B) <= C
End Function
Function ExtendRslt(CurrRslt, NewVal, Separator)
If CurrRslt = "" Then ExtendRslt = NewVal _
Else ExtendRslt = CurrRslt & Separator & NewVal
End Function
Sub recursiveMatch(ByVal MaxSoln As Integer, ByVal TargetVal, InArr(),
_
ByVal CurrIdx As Integer, _
ByVal CurrTotal, ByVal Diff As Double, _
ByRef Rslt(), ByVal CurrRslt As String, ByVal Separator As
String)
Dim I As Integer
For I = CurrIdx To UBound(InArr)
If RealEqual(CurrTotal + InArr(I), TargetVal, Diff) Then
Rslt(UBound(Rslt)) = ExtendRslt(CurrRslt, I, Separator)
If MaxSoln = 0 Then
If UBound(Rslt) Mod 100 = 0 Then Debug.Print UBound
(Rslt) & "=" & Rslt(UBound(Rslt))
Else
If UBound(Rslt) >= MaxSoln Then Exit Sub
End If
ReDim Preserve Rslt(UBound(Rslt) + 1)
ElseIf CurrTotal + InArr(I) > TargetVal + Diff Then
ElseIf CurrIdx < UBound(InArr) Then
recursiveMatch MaxSoln, TargetVal, InArr(), I + 1, _
CurrTotal + InArr(I), Diff, Rslt(), _
ExtendRslt(CurrRslt, I, Separator), _
Separator
If MaxSoln <> 0 Then If UBound(Rslt) >= MaxSoln Then Exit
Sub
Else
'we've run out of possible elements and we _
still don't have a match
End If
Next I
End Sub
Function ArrLen(Arr()) As Integer
On Error Resume Next
ArrLen = UBound(Arr) - LBound(Arr) + 1
End Function
Sub startSearch()
'The selection should be a single contiguous range in a single
column. _
The first cell indicates the number of solutions wanted. Specify
zero for all. _
The 2nd cell is the target value. _
The 3rd cell sets the range around the target value. _
The rest of the cells are the values available for matching. _
The output is in the column adjacent to the one containing the
input data.
Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As
Integer, Diff As Double
MaxSoln = Selection.Cells(1).Value
TargetVal = Selection.Cells(2).Value
Diff = Selection.Cells(3).Value
InArr = Application.WorksheetFunction.Transpose( _
Selection.Offset(3, 0).Resize(Selection.Rows.Count - 3).Value)
ReDim Rslt(0)
recursiveMatch MaxSoln, TargetVal, InArr, LBound(InArr), 0, Diff,
_
Rslt, "", ", "
Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
Application.WorksheetFunction.Transpose(Rslt)
End Sub

 

[catpol]

Get SumMatch Excel add-in.

This add-in does exactly what you need. You can specify accuracy of the solution.

 

[JB]

Starting with a column of ~30 numbers, I'm looking for a way to have
Excel find all combinations that sum to a target value +/- a specified
range width. (For example, I may be looking for all combinations that
sum to 1000 +/- 10.)

I've found some code from Tushar Mehta that finds a list of all
possible matches that sum to a number, but it doesn't allow me to put
in a range to find sums to within a delta of this value. I tried
modifying the code to add this in, but I couldn't get it to work
right. Here is the code: http://www.tushar-mehta.com/excel/templates/match_values/index.html

Does anyone have any suggestions?

Thank you,
Laura

There is an Excel add-in - SumMatch from SumMatch.com.
It allows matching set of values to a given sum with a given accuracy.

 

[h2so4]

JB has brought this to us :

There is an Excel add-in - SumMatch from SumMatch.com.
It allows matching set of values to a given sum with a given accuracy.

hello,

to my opinion, you don't need to change the code, just set epsilon to
the desired value.