I have a difficult problem using the combin function that I can't seem to find a solution for online.
I have daily performances of 10 different trading strategies and I want to display and rank the top performing combination of 4 of them every day. (So in excel this amounts to 210 different combinations per day using the function COMBIN(10,4).
The issue I am having is that Im not sure how to display the various combinations as output, which I can then rank and use for statistical interpretation.
On the active sheet,
Put your trading symbols in A1:J1 (10 symbols)
then run this macro:
Dim n As Integer, m As Integer
Dim v As Variant, rng As Range
numcomb = 0
Set rng = Range("A1:J1")
'Set rng = rng.Resize(1, 5)
v = Application.Transpose(Application _
n = UBound(v, 1)
' m = InputBox("Taken how many at a time?", "Combinations")
m = 4
If Application.Combin(n, m) > 64530 Then
MsgBox "Too many to write out, quitting"
Comb2 n, m, 1, "'", v
'Generate combinations of integers k..n taken m at a time, recursively
Sub Comb2(ByVal n As Integer, ByVal m As Integer, _
ByVal k As Integer, ByVal s As String, v As Variant)
Dim v1 As Variant
If m > n - k + 1 Then Exit Sub
If m = 0 Then
'Debug.Print "->" & s & "<-"
v1 = Split(Replace(Trim(s), "'", ""), " ")
For i = LBound(v1) To UBound(v1)
ActiveCell.Offset(0, i) = v(v1(i))
Comb2 n, m - 1, k + 1, s & k & " ", v
Comb2 n, m, k + 1, s, v
This will generate the 210 Unique combinations starting in row 3, column A