Formula to calculate combinations of numbers.

[pagination1]

Hi there,

I’d be really grateful for your help, as this is driving me mad.

If I have an excel spreadsheet, with, for example,
cells A1:A8 each containing a number from 1 to 8 (eg, A1=1, A2=2, A3=3 etc), how can I do the following:

I would like a formula which can tell me how many FOUR digit combinations there are of the numbers contained in A1:A8 BUT, the four digit combinations produced by the formula must total 15 exactly. No more, no less.

Does anyone have an idea what on earth this formula might be?

I’ve spent hours trying to figure it out, but I just can’t work it out.

Thank you so much in advance for your help!

 

[pagination1]

I should add that I don’t mind in which cells each of the 4 digit combinations are produced.

Let’s say from A9 onwards (A10, A11 etc), for the purposes of this example.

Thank you!

 

[pagination1]

I should further add, to be clear, that I don’t want just the total number of 4 digit combinations (eg, 256), I would like each 4 digit number combination, totalling 15 exactly, produced in cells A9 onwards.

I don’t even know if this is possible with Excel.

Duplications of numbers are also fine Eg: 1,3,3,8 is just fine.

It also makes no difference to me if the 3rd and 2nd numbers (and vice versa) in the above example are swapped with each other, as the output is still 1,3,3,8.

 

[mikerickson]

Are duplicates allowed? or must each A1:A8 be used once at most?

 

[pagination1]

Are duplicates allowed? or must each A1:A8 be used once at most?

Hi Mike,

Thank you for your response.

The numbers specified in A1:A8 may only be used once for each calculation. So, the formula can't use the value in any of those cells more than once (per calculation), in order to calculate the 4 digit combinations.

 

[mikerickson]

From that I presume that you want only the 4 element solutions, a 3 element solution would not be counted.
Do you want only the count or do you want the actual elements?

 

[pagination1]

From that I presume that you want only the 4 element solutions, a 3 element solution would not be counted.
Do you want only the count or do you want the actual elements?

Hi,

It must be exactly a four element solution only.

I would need the actual elements. The count doesn’t really matter.

Best,

 

[pagination1]

Hi,

It must be exactly a four element solution only.

I would need the actual elements. The count doesn’t really matter.

Best,

Apologies, I was incorrect in a statement I made - you MAY duplicate the numbers specified in A1:A8

For example, if A3 has the number “3” and A4 has the number “4”, then a valid combination would be

4,4,4,3

This totals 15.

You will see that the value in A3 has been used three times, and the value in A4 has been used once.

Apologies for my error.

 

[mikerickson]

This should do what you want

Sub test()
    Dim i1 As Long, i2 As Long, i3 As Long, i4 As Long
    Dim term1 As Double, term2 As Double, term3 As Double, term4 As Double
    Dim sumSought As Double
    Dim rngOutput As Range
    Set rngOutput = Range("D:D")
    sumSought = 15
    
    rngOutput.Resize(1, 4).EntireColumn.ClearContents
    
    With Range("A1:A8")
        For i1 = 1 To .Cells.Count
        term1 = Val(.Cells(i1, 1))
            For i2 = 1 To .Cells.Count
            term2 = Val(.Cells(i2, 1))
                For i3 = 1 To .Cells.Count
                    term3 = Val(.Cells(i3, 1))
                    For i4 = 1 To .Cells.Count
                        term4 = Val(.Cells(i4, 1).Value)
                        If term1 + term2 + term3 + term4 = sumSought Then
                            With rngOutput.EntireColumn
                                With .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                                    .Cells(1, 1) = term1
                                    .Cells(1, 2) = term2
                                    .Cells(1, 3) = term3
                                    .Cells(1, 4) = term4
                                End With
                            End With
                        End If
                    Next i4
                Next i3
            Next i2
        Next i1
    End With
End Sub

 

[pagination1]

It works perfectly! Thank you so much for this, I really appreciate it.