combination question

[robfl22]

Hi,

I have 25 numbers numbered 1 thru 25. Is there a formula that could tell me how many combinations of 5 numbers would add up to a total between 68 and 71. And is there a way to list all of those combinations?

Thank you in advance.

 

[Luke M]

I doubt a single formula could figure this out. Using this macro, total combinations appears to be 375,408.

Sub FindNumbers()
    Dim i1 As Integer
    Dim i2 As Integer
    Dim i3 As Integer
    Dim i4 As Integer
    Dim i5 As Integer
    Dim testSum As Integer
  
    Dim totCount As Long
  
    Const limUp As Integer = 25
    Const limDown As Integer = 1
  
    Const sumUp As Integer = 71
    Const sumDown As Integer = 68
  
    totCount = 0
  
    For i1 = limDown To limUp
        For i2 = limDown To limUp - 1
            If i2 = i1 Then GoTo skipI2
            For i3 = limDown To limUp - 2
                If i3 = i1 Or i3 = i2 Then GoTo skipI3
                For i4 = limDown To limUp - 3
                    Select Case i4
                  
                    Case i1, i2, i3
                        'Do nothing
                    Case Else
                        For i5 = limDown To limUp - 4
                            Select Case i5
                                Case i1, i2, i3, i4
                                    'Do nothing
                                Case Else
                                    testSum = i1 + i2 + i3 + i4 + i5
                                  
                                    If testSum >= sumDown And testSum <= sumUp Then
                                        totCount = totCount + 1
                                    End If
                            End Select
                        Next i5
                    End Select
                Next i4
skipI3:
            Next i3
skipI2:
        Next i2
    Next i1
                              
    MsgBox "Combinations found: " & totCount
  
End Sub

Code runs fairly fast atm, since it doesn't have to write anything. If you want to record the combos (and have time to run macro), run this one.

Sub FindNumbersPrint()
    Dim i1 As Integer
    Dim i2 As Integer
    Dim i3 As Integer
    Dim i4 As Integer
    Dim i5 As Integer
    Dim testSum As Integer
  
    Dim totCount As Long
  
    Const limUp As Integer = 25
    Const limDown As Integer = 1
  
    Const sumUp As Integer = 71
    Const sumDown As Integer = 68
  
    totCount = 0
    Application.ScreenUpdating = False
    For i1 = limDown To limUp
        For i2 = limDown To limUp - 1
            If i2 = i1 Then GoTo skipI2
            For i3 = limDown To limUp - 2
                If i3 = i1 Or i3 = i2 Then GoTo skipI3
                For i4 = limDown To limUp - 3
                    Select Case i4
                  
                    Case i1, i2, i3
                        'Do nothing
                    Case Else
                        For i5 = limDown To limUp - 4
                            Select Case i5
                                Case i1, i2, i3, i4
                                    'Do nothing
                                Case Else
                                    testSum = i1 + i2 + i3 + i4 + i5
                                    'If totCount = 10000 Then Stop
                                    If testSum >= sumDown And testSum <= sumUp Then
                                        totCount = totCount + 1
                                        Cells(totCount, "A").Value = i1 & ", " & i2 & ", " & i3 & ", " & i4 & ", " & i5
                                    End If
                            End Select
                        Next i5
                    End Select
                Next i4
skipI3:
            Next i3
skipI2:
        Next i2
    Next i1
    Application.ScreenUpdating = True
    MsgBox "Combinations found: " & totCount
  
End Sub

 

[bines53]

Hi,

Is a combination of five numbers, whether the numbers can be several times?
In other words, this combination, for example,14,14,14,14,14=70 ,It's OK ?

David

 

[Luke M]

Note that this considers:
25, 24, 19, 1, 2
and
25, 24, 19, 2, 1

as two different possible combinations. Not sure if that's what you want or not.

 

[Luke M]

If order doesn't matter, and no digits can repeat

Sub FindNumbers()
    Dim i1 As Integer
    Dim i2 As Integer
    Dim i3 As Integer
    Dim i4 As Integer
    Dim i5 As Integer
    Dim testSum As Integer
   
    Dim totCount As Long
   
    Const limUp As Integer = 25
    Const limDown As Integer = 1
   
    Const sumUp As Integer = 71
    Const sumDown As Integer = 68
   
    totCount = 0
    Application.ScreenUpdating = False
    For i1 = limDown To limUp
        For i2 = i1 To limUp
            If i2 = i1 Then GoTo skipI2
            For i3 = i2 To limUp
                If i3 = i1 Or i3 = i2 Then GoTo skipI3
                For i4 = i3 To limUp
                    Select Case i4
                    Case i1, i2, i3
                        'Do nothing
                    Case Else
                        For i5 = i4 To limUp
                            Select Case i5
                                Case i1, i2, i3, i4
                                    'Do nothing
                                Case Else
                                    testSum = i1 + i2 + i3 + i4 + i5
                                    'If totCount = 10000 Then Stop
                                    If testSum >= sumDown And testSum <= sumUp Then
                                        totCount = totCount + 1
                                        Cells(totCount, "A").Value = i1 & ", " & i2 & ", " & i3 & ", " & i4 & ", " & i5
                                    End If
                            End Select
                        Next i5
                    End Select
                Next i4
skipI3:
            Next i3
skipI2:
        Next i2
    Next i1
    Application.ScreenUpdating = True
    MsgBox "Combinations found: " & totCount
   
End Sub

solution had 5,338 combos this way.

 

[robfl22]

Guys thanks. Yes I forgot to add that no numbers could repeat. This last one is good Luke