Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing combinations in excel
I have a series of 7 events, matches, each with three possible outcomes, 0, 1 and 3. I want to list in excel all the possible combinations that could result from these matches. So it is just 3 to the power of 7. I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc. I have started trying to build a macro to do it but failed miserably. If anyone can offer any pointers it would be much appreciated. Thanks -- Raigmore ------------------------------------------------------------------------ Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071 View this thread: http://www.excelforum.com/showthread...hreadid=518960 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing combinations in excel
So you have 2187 combinations, expressed in a number system base 3 (although
you don't want 2's but 3's, but that's just presentation, doesn't alter the approach) I used Ron Rosenfeld's function to convert your decimal numbers (base 10) to base 3 numbers, converted them to text with 7 digits and replaced the 2's with 3s. Here's the formula, starting in A1 and to be filled down to A2187: =SUBSTITUTE(TEXT(BaseConvert(ROW()-1,10,3),"0000000"),"2","3") and here's Ron's code: ' ================================================== Function BaseConvert(Num, FromBase As Integer, _ ToBase As Integer, Optional DecPlace As Long) _ As String 'by Ron Rosenfeld 'Handles from base 2 to base 62 by differentiating small and capital letters Dim LDI As Integer 'Leading Digit Index Dim i As Integer, j As Integer Dim Temp, Temp2 Dim Digits() Dim r Dim DecSep As String DecSep = Application.International(xlDecimalSeparator) On Error GoTo HANDLER If FromBase 62 Or ToBase 62 _ Or FromBase < 2 Or ToBase < 2 Then BaseConvert = "Base out of range" Exit Function End If If InStr(1, Num, "E") And FromBase = 10 Then Num = CDec(Num) End If 'Convert to Base 10 LDI = InStr(1, Num, DecSep) - 2 If LDI = -2 Then LDI = Len(Num) - 1 j = LDI Temp = Replace(Num, DecSep, "") For i = 1 To Len(Temp) Temp2 = Mid(Temp, i, 1) Select Case Temp2 Case "A" To "Z" Temp2 = Asc(Temp2) - 55 Case "a" To "z" Temp2 = Asc(Temp2) - 61 End Select If Temp2 = FromBase Then BaseConvert = "Invalid Digit" Exit Function End If r = CDec(r + Temp2 * FromBase ^ j) j = j - 1 Next i If r < 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase))) If r < 1 Then LDI = 0 ReDim Digits(LDI) For i = UBound(Digits) To 0 Step -1 Digits(i) = Format(Fix(r / ToBase ^ i)) r = CDbl(r - Digits(i) * ToBase ^ i) Select Case Digits(i) Case 10 To 35 Digits(i) = Chr(Digits(i) + 55) Case 36 To 62 Digits(i) = Chr(Digits(i) + 61) End Select Next i Temp = StrReverse(Join(Digits, "")) 'Integer portion ReDim Digits(DecPlace) If r < 0 Then Digits(0) = DecSep For i = 1 To UBound(Digits) Digits(i) = Format(Fix(r / ToBase ^ -i)) r = CDec(r - Digits(i) * ToBase ^ -i) Select Case Digits(i) Case 10 To 35 Digits(i) = Chr(Digits(i) + 55) Case 36 To 62 Digits(i) = Chr(Digits(i) + 61) End Select Next i End If BaseConvert = Temp & Join(Digits, "") Exit Function HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _ "Number being converted: " & Num) End Function ' ================================================== -- Kind regards, Niek Otten "Raigmore" wrote in message ... I have a series of 7 events, matches, each with three possible outcomes, 0, 1 and 3. I want to list in excel all the possible combinations that could result from these matches. So it is just 3 to the power of 7. I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc. I have started trying to build a macro to do it but failed miserably. If anyone can offer any pointers it would be much appreciated. Thanks -- Raigmore ------------------------------------------------------------------------ Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071 View this thread: http://www.excelforum.com/showthread...hreadid=518960 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing combinations in excel
Try:
Dim vals(7), cary As Integer Sub combinationlist() Dim i As Integer Dim j As Long Dim s As String ' gsnu For i = 1 To 7 vals(i) = 0 Next Cells(1, 1).Value = "0,0,0,0,0,0,0" For j = 2 To 3 ^ 7 For i = 1 To 7 If i = 1 Then cary = 1 End If Call bump(i) Next s = "" For i = 1 To 7 s = vals(i) & "," & s Next Cells(j, 1).Value = Left(s, 13) Next End Sub Sub bump(i) If cary = 0 Then Exit Sub End If If vals(i) = 0 Then vals(i) = 1 cary = 0 ElseIf vals(i) = 1 Then vals(i) = 3 cary = 0 Else vals(i) = 0 cary = 1 End If End Sub -- Gary''s Student "Raigmore" wrote: I have a series of 7 events, matches, each with three possible outcomes, 0, 1 and 3. I want to list in excel all the possible combinations that could result from these matches. So it is just 3 to the power of 7. I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc. I have started trying to build a macro to do it but failed miserably. If anyone can offer any pointers it would be much appreciated. Thanks -- Raigmore ------------------------------------------------------------------------ Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071 View this thread: http://www.excelforum.com/showthread...hreadid=518960 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing combinations in excel
If you need the digits in separate cells, put this in B1:
=MID($A1,COLUMN(A1),1) Copy Right to H1 and then copy down. -- Kind regards, Niek Otten "Niek Otten" wrote in message ... So you have 2187 combinations, expressed in a number system base 3 (although you don't want 2's but 3's, but that's just presentation, doesn't alter the approach) I used Ron Rosenfeld's function to convert your decimal numbers (base 10) to base 3 numbers, converted them to text with 7 digits and replaced the 2's with 3s. Here's the formula, starting in A1 and to be filled down to A2187: =SUBSTITUTE(TEXT(BaseConvert(ROW()-1,10,3),"0000000"),"2","3") and here's Ron's code: ' ================================================== Function BaseConvert(Num, FromBase As Integer, _ ToBase As Integer, Optional DecPlace As Long) _ As String 'by Ron Rosenfeld 'Handles from base 2 to base 62 by differentiating small and capital letters Dim LDI As Integer 'Leading Digit Index Dim i As Integer, j As Integer Dim Temp, Temp2 Dim Digits() Dim r Dim DecSep As String DecSep = Application.International(xlDecimalSeparator) On Error GoTo HANDLER If FromBase 62 Or ToBase 62 _ Or FromBase < 2 Or ToBase < 2 Then BaseConvert = "Base out of range" Exit Function End If If InStr(1, Num, "E") And FromBase = 10 Then Num = CDec(Num) End If 'Convert to Base 10 LDI = InStr(1, Num, DecSep) - 2 If LDI = -2 Then LDI = Len(Num) - 1 j = LDI Temp = Replace(Num, DecSep, "") For i = 1 To Len(Temp) Temp2 = Mid(Temp, i, 1) Select Case Temp2 Case "A" To "Z" Temp2 = Asc(Temp2) - 55 Case "a" To "z" Temp2 = Asc(Temp2) - 61 End Select If Temp2 = FromBase Then BaseConvert = "Invalid Digit" Exit Function End If r = CDec(r + Temp2 * FromBase ^ j) j = j - 1 Next i If r < 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase))) If r < 1 Then LDI = 0 ReDim Digits(LDI) For i = UBound(Digits) To 0 Step -1 Digits(i) = Format(Fix(r / ToBase ^ i)) r = CDbl(r - Digits(i) * ToBase ^ i) Select Case Digits(i) Case 10 To 35 Digits(i) = Chr(Digits(i) + 55) Case 36 To 62 Digits(i) = Chr(Digits(i) + 61) End Select Next i Temp = StrReverse(Join(Digits, "")) 'Integer portion ReDim Digits(DecPlace) If r < 0 Then Digits(0) = DecSep For i = 1 To UBound(Digits) Digits(i) = Format(Fix(r / ToBase ^ -i)) r = CDec(r - Digits(i) * ToBase ^ -i) Select Case Digits(i) Case 10 To 35 Digits(i) = Chr(Digits(i) + 55) Case 36 To 62 Digits(i) = Chr(Digits(i) + 61) End Select Next i End If BaseConvert = Temp & Join(Digits, "") Exit Function HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _ "Number being converted: " & Num) End Function ' ================================================== -- Kind regards, Niek Otten "Raigmore" wrote in message ... I have a series of 7 events, matches, each with three possible outcomes, 0, 1 and 3. I want to list in excel all the possible combinations that could result from these matches. So it is just 3 to the power of 7. I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc. I have started trying to build a macro to do it but failed miserably. If anyone can offer any pointers it would be much appreciated. Thanks -- Raigmore ------------------------------------------------------------------------ Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071 View this thread: http://www.excelforum.com/showthread...hreadid=518960 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing combinations in excel
Thanks gents, that is great and your help is much appreciated. It does the job perfectly and I will be able to use the info again for other tasks. -- Raigmore ------------------------------------------------------------------------ Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071 View this thread: http://www.excelforum.com/showthread...hreadid=518960 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing combinations in excel
Good to hear that,
Next time, use =SUBSTITUTE(TEXT(BaseConvert(ROW(A1)-1,10,3),"0000000"),"2","3") so you don't necessarily have to start in row 1 and so can include headers if you wish. -- Kind regards, Niek Otten "Raigmore" wrote in message ... Thanks gents, that is great and your help is much appreciated. It does the job perfectly and I will be able to use the info again for other tasks. -- Raigmore ------------------------------------------------------------------------ Raigmore's Profile: http://www.excelforum.com/member.php...o&userid=30071 View this thread: http://www.excelforum.com/showthread...hreadid=518960 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listing combinations in excel
In article ,
Raigmore wrote: I want to list in excel all the possible combinations that could result from these matches. So it is just 3 to the power of 7. I would like them listed as 0,0,0,0,0,0,0 then 0,0,0,0,0,0,1 etc etc. [ except with 3 instead of 2 for the third possible digit ] This doesn't need macros; it can be done with functions. You'll be filling a 7-column by 2187-row block with these. What follows assumes that the top left corner will be at cell B3, so the bottom right corner is in cell H2189. Fill the first row cells, B3 through H3, with zeroes. Next, in the rightmost column, start at the second row of the block, cell H4, and put in the formula: =IF(H3=0,1,IF(H3=3,0,1)) and copy it down, so it fills every cell of that right column. This cycles 0,1,3 all down the column. Now, put the following formula (which is similar to that one, but with a prefix) in the top right still-empty cell, G4: =IF(H40,G3,IF(G3=0,1,IF(G3=3,0,1))) and copy that to all the remaining cells, B4 thru G2189. If I didn't screw any of that up, you now have what I think you asked for. -- Randy Hudson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Excel to Calculate All Combinations of a Set of Data? | Excel Worksheet Functions | |||
Can excel list combinations | Excel Worksheet Functions | |||
Combinations from group in Excel | Excel Programming | |||
Excel combinations from groups | Excel Discussion (Misc queries) | |||
Generating excel combinations | Excel Discussion (Misc queries) |