combination of numbers that equal a target

  • Thread starter Thread starter jimbo
  • Start date Start date
J

jimbo

Hi I have a list of numbers and a list of target numbers. I wish to
find out which combination of numbers will equal to a target number.

eg my target is 10.50 and I have the following list:

5,3,2,1.5,4

the solution will be 5,4,1.5 or 3,2,4,1.5
 
jimbo,

See the message below.

HTH,
Bernie
MS Excel MVP

'I was asked by a colleague to find the combination of certain numbers
'which will add up to a specific value. The numbers I was given were:
'
' 52.04;57.63;247.81;285.71;425.00;690.72;764.57;1485.00;1609.24;
' 3737.45;6485.47;6883.85;7309.33;12914.64;13714.11;14346.39;
' 15337.85;22837.83;31201.42;34663.07;321987.28
'
' (21 numbers in ascending order)
'
' I am trying to get a combination so that it adds up to 420422.19.
'
' On a sheet, put the following
' B1 Target 420422.19
' B2 number of parameters 21
' B3:B23 all parameters
' 321987.28
' 34663.07
' 31201.42
' 22837.83
' 15337.85
' 14346.39
' 13714.11
' 12914.64
' 7309.33
' 6883.85
' 6485.47
' 3737.45
' 1609.24
' 1485
' 764.57
' 690.72
' 425
' 285.71
' 247.81
' 57.63
' 52.04
' Start find_sol, it will put "1" or "0" in C3:Cx if you sum the
' parameters with a "1", you will have the best solution.
' It takes about 12 seconds on my very slow P133.
' The solution is
' 1 1 0 1 0 0 1 1 1 0 0 1 1 0 0 1 1 1 1 0 0
' Regards.
'
' Michel.
' Michel Claes <[email protected]>


Option Explicit

Global target As Double
Global nbr_elem As Integer
Global stat(30) As Integer
Global statb(30) As Integer
Global elems(30) As Double
Global best As Double

Sub store_sol()
Dim i As Integer
For i = 1 To nbr_elem
Cells(i + 2, 3) = statb(i)
Next i
End Sub

Sub copy_stat()
Dim i As Integer
For i = 1 To nbr_elem
statb(i) = stat(i)
Next i
End Sub

Sub eval(ByVal total As Double, ByVal pos As Integer)
If pos <= nbr_elem Then
stat(pos) = 0
eval total, pos + 1
stat(pos) = 1
eval total + elems(pos), pos + 1
Else
If (Abs(total - target) < Abs(target - best)) Then
best = total
copy_stat
End If
End If
End Sub

Sub find_sol()
Dim i As Integer
best = 0
target = Cells(1, 2)
nbr_elem = Cells(2, 2)
For i = 1 To nbr_elem
elems(i) = Cells(i + 2, 2)
Next i
eval 0, 1
store_sol
End Sub
 
Back
Top