binomial coefficient - n choose k

[Linda]

I know how to calculate how many combinations there are, but how do I determine what the combinations are? I need 12 choose 4, how can excel tell me what the 495 different combinations are?

 

[Jay Petrulis]

Hi,

Generating the list of combinations is not easy. For any flexibility, you would want to use a recursive function.

Search the Max Cells Lounge for the Mr. Excel August Challenge (or the MrExcel site proper), for some examples of recursive combination generating functions.

For a specific, one-off answer, the following code will do as you want. You can change the TotalNum , but changing the number chosen (k) is not as easy.

<pre>Sub combins_choose_4()

Const TotalNum As Long = 12
Dim x As Long, y As Long
Dim z As Long, w As Long
Dim Counter As Long
Dim Combin_Array
ReDim Combin_Array(1 To Application.Combin(TotalNum, 4))

For w = 1 To TotalNum - 3
For x = w + 1 To TotalNum - 2
For y = x + 1 To TotalNum - 1
For z = y + 1 To TotalNum
Counter = Counter + 1
Combin_Array(Counter) = w & " | " & x & " | " _
& y & " | " & z
Next z
Next y
Next x
Next w

ActiveSheet.Range("A1").Resize(UBound(Combin_Array), 1) = _
Application.Transpose(Combin_Array)

End Sub</pre>

Rather than reporting just the index numbers, you can also report the actual array elements. Something like this:

<pre>Sub combins_choose_4()

Const TotalNum As Long = 12
Dim x As Long, y As Long
Dim z As Long, w As Long
Dim Counter As Long
Dim Combin_Array
Dim Choice_Array

ReDim Choice_Array(1 To TotalNum)
ReDim Combin_Array(1 To Application.Combin(TotalNum, 4))

For x = 1 To TotalNum
Choice_Array(x) = Chr(x + 64)
Next x


For w = 1 To TotalNum - 3
For x = w + 1 To TotalNum - 2
For y = x + 1 To TotalNum - 1
For z = y + 1 To TotalNum
Counter = Counter + 1
Combin_Array(Counter) = _
Choice_Array(w) & " | " & Choice_Array(x) & " | " _
& Choice_Array & " | " & Choice_Array(z)
Next z
Next y
Next x
Next w

ActiveSheet.Range("A1").Resize(UBound(Combin_Array), 1) = _
Application.Transpose(Combin_Array)

End Sub</pre>

 

[Mark W.]

See my posting.

 

[Linda]

Thankx Jay, I have managed to use your code and will look into combination generating functions as you advised.

MArk, I am intrigued how you use ODBC drivers and sql code? I can create sql queries in Access, but do not follow how you got your answer into excel column c?

 

[Mark W.]

I named the range A2:A5 as 'List', saved the workbook, and selected the Data | Get External Data | New Database Query... menu command. From there I picked the Excel ODBC driver and identified the saved workbook. 'List' is then treated as a table with a single column, 'Item'. The 'List' table was added twice (Table A and B). The rest is in the SQL code.