Number Generator

There are 1,215,450 such combinations. What would you do after you listed them?

You could use a UDF:

Function aiComboByNum(ByVal n As Long, ByVal t As Long, ByVal cNum As Long) As Long()
    ' shg 2009-12

    ' VBA or Worksheet Function

    ' Does the opposite of iComboNum; i.e., given a combination number
    ' [0, Comb(n,t)-1], returns the combination.

    ' E.g., for 5 choose 3, the first (0th) combination is {2,1,0}, and
    '                       the last  (9th) combination is {4,3,2}

    ' n is not strictly necessary to the computation, but makes it quicker.

    Dim ai()        As Long
    Dim i           As Long
    Dim j           As Long

    If cNum >= Comb(n, t) Or cNum < 0 Then Exit Function
    
    ReDim ai(1 To t)

    For i = t To 1 Step -1
        Do
            n = n - 1
            j = Comb(n, i)
        Loop While j > cNum

        ai(t - i + 1) = n
        cNum = cNum - j
    Next i

    aiComboByNum = ai
End Function

Function Comb(n As Long, t As Long) As Long
    ' Just like COMBIN(n, t) except t > n returns 0
    Dim iDlt        As Long
    Dim iMax        As Long
    Dim i           As Long

    If n < 0 Or t < 0 Then Exit Function

    Select Case Sgn(n - t)
        Case -1
            Comb = 0
        Case 0
            Comb = 1
        Case Else
            If n > 2 * t Then
                iDlt = n - t
                iMax = t
            Else
                iDlt = t
                iMax = n - t
            End If

            Comb = iDlt + 1
            For i = 2 To iMax
                Comb = (Comb * (iDlt + i)) / i
            Next i
    End Select
End Function

To pick a random combination of 75 choose 5, select a 4-wide array of cells and array-enter this formula:

=aiComboByNum(75, 4, RANDBETWEEN(0, COMBIN(75, 4) - 1))

The first (zeroth) combination is

=aiComboByNum(75, 4, 0) = 3 2 1 0

and the last is

=aiComboByNum(75, 4, 1215449) = 74 73 72 71

Yes.

Adding a Macro to a Code Module – Excel 2007
1. Copy the code from the post
2. Click the Developer tab, then Visual Basic
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel

there is a typo in the code the first line should read

Function aiComboByNum(ByVal n As Long, ByVal t As Long, ByVal cNum As Long)

dont forget to array enter it with ctrl+shift+ enter
see here
http://office.microsoft.com/en-us/ex...872901033.aspx

Originally Posted by Chris

I went to the blank work sheet and entered: =aiComboByNum(75, 4, RANDBETWEEN(0, COMBIN(75, 4) - 1))
and the result is : #NAME?

Sounds like you didn't get the code installed. See attached. Before you open the workbook, set macro security to Disable all macros with notification, and then enable macros when prompted.

Originally Posted by Martin

there is a typo in the code the first line should read

There's no typo, Martin, it's correct as written.

Originally Posted by Martin

dont forget to array enter it with ctrl+shift+ enter

True, thanks for the emphasis.

hm it fails like that but works with the correction tho. i get name error using as you posted but it gives the results you posted when the last As Long() is omited then again i didnt test it in 2007 but in 97
edit just tried in 2007 ok hmmmmmmmmmmmm

I tested in 2003 and 2007; don't have anything older.

i see what you mean ,must be a version thing