I can use this to give me the number of possible combinations, in this
case 3 from 26, is there anyway I can produced a table of possible
results if we assume 1=a, 2=b 3=c etc. I am new to excel and want to
look at combinations and their results.
As I understand it, you would like to see the combinations of "A"
through "Z" taken 3 at a time; for example, ABC, ABD,..., ABZ, ACD,
ACE, etc.
Note: COMBIN counts the number of sets of 3-letter combinations; so
ABC, ACB etc are counted as one. I wonder if you want PERMUT, which
counts the number of permutations of each 3-letter set; so ABC, ACB,
BAC, BCA, CAB and CBA each count. On the other hand, I wonder if you
to count arrangements like AAA, AAB, AAC etc (permutations with
replacement). That is counted by the formula 26^3.
In any case, to answer your question: "Is there any way I can
produced a table of possible results?".
There is (almost) always "a way". The question is: Is it feasible to
implement and execute?
In part, that depends on the COMBIN (or PERMUT or n^c) parameters;
that is, the number of rows produced by all of the arrangements.
You neglect to mention what version of Excel you are using. But for
any version, the number of combinations or permutations with or
without replaces might be too large to consider feasible.
In the case of 26 letters taken 3 at a time, all three types of
arrangements are feasible: COMBIN(26,3) is 2600; PERMUT(26,3) is
15,600; and 26^3 is 17,576.
See the following VBA macros. To use VBA macros (at least in Excel
2003):
1. In Excel, open or select a worksheet.
2. Press alt+F11 to open a VBA window.
3. In VBA, click Insert > Module, or select an existing module. This
should open a VBA editing pane.
4. Copy the macros below (highlight text and press ctrl+C), and paste
into the VBA editing pane (ctrl+V).
5. Edit the macros as needed. See the comment after the Sub line.
6. In Excel, select the first cell where you can the column of
arrangements.
7. Press alt+F8, and run the desired macro.
PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
Macros....
Option Explicit
Sub doPermut_withReplacement() '26^3 arrangements
'Change cA as needed
Const cA As Long = 65 'A=65, a=97
Dim i As Long, j As Long, k As Long, n As Long
Dim ci As String * 1, cj As String * 1, ck As String * 1
Dim r As Range
Application.ScreenUpdating = False
Set r = Selection: n = 0
For i = 0 To 25
ci = Chr(i + cA)
For j = 0 To 25
cj = Chr(j + cA)
For k = 0 To 25
ck = Chr(k + cA)
r.Offset(n) = ci & cj & ck
n = n + 1
Next k: Next j: Next i
Application.ScreenUpdating = True
MsgBox n & " in " & Selection.Resize(n).Address
End Sub
Sub doPermut_withoutReplacement() 'PERMUT(26,3) arrangements
'Change cA as needed
Const cA As Long = 65 'A=65, a=97
Dim i As Long, j As Long, k As Long, n As Long
Dim ci As String * 1, cj As String * 1, ck As String * 1
Dim r As Range
Application.ScreenUpdating = False
Set r = Selection: n = 0
For i = 0 To 25
ci = Chr(i + cA)
For j = 0 To 25
cj = Chr(j + cA)
If ci <> cj Then
For k = 0 To 25
ck = Chr(k + cA)
If ci <> ck And cj <> ck Then
r.Offset(n) = ci & cj & ck
n = n + 1
End If
Next k
End If
Next j: Next i
Application.ScreenUpdating = True
MsgBox n & " in " & Selection.Resize(n).Address
End Sub
Sub doCombin() 'COMBIN(26,3) arrangements
'Change cA as needed
Const cA As Long = 65 'A=65, a=97
Dim i As Long, j As Long, k As Long, n As Long
Dim ci As String * 1, cj As String * 1, ck As String * 1
Dim r As Range
Application.ScreenUpdating = False
Set r = Selection: n = 0
For i = 0 To 23
ci = Chr(i + cA)
For j = i + 1 To 24
cj = Chr(j + cA)
For k = j + 1 To 25
ck = Chr(k + cA)
r.Offset(n) = ci & cj & ck
n = n + 1
Next k: Next j: Next i
Application.ScreenUpdating = True
MsgBox n & " in " & Selection.Resize(n).Address
End Sub
