Combinations & Permutations with Replacement (Repetition)

[JenniferMurphy]

As I understand it, the combin & permut functions only calculate the results without replacement. There are no corresponding functions for with replacement. Is that correct?

The formula for permutations with replacement is just n^k, but the one for combinations is ugly: (n+k-1)!/k!(n-1)!.

Do I need to write a UDF to avoid having to enter that each time and probably make a typo 30% of the time?

Would it have killed the M$FT developers to have added a third parameter to these functions, something like 0/1 where 0 = without replacement and 1 = with? They could have set the default to without replacement so that there would be no compatibility problems. Seems obvious and simple.

 

[JackDanIce]

As far as I'm aware you'll need to write your own UDF, this is one suggestion:

Function Combinations_Replacement(ByRef k As Long, ByRef n As Long) As Double
 
    Dim x               As Long
    Dim numer           As Long
    Dim denom(1 To 2)   As Long
    
    '(n-1+k)!/k!(n-1)!
    n = n - 1
    
    For x = 1 To n + k
        numer = numer + x
        If x <= k Then denom(1) = denom(1) + x
        If x <= n Then denom(2) = denom(2) + x
    Next x
    
    Combinations_Replacement = numer / (denom(1) * denom(2))
    
    Erase denom
    
End Function

 

[JackDanIce]

Sorry, misread factorial, try:

Function Combinations_Replacement(ByRef k As Long, ByRef n As Long) As Double
 
    Dim x               As Long
    Dim numer           As Long
    Dim denom(1 To 2)   As Long
    
    '(n-1+k)!/k!(n-1)!
    n = n - 1
    numer = 1: demon(1) = 1: denom(2) = 1
    
    For x = 1 To n + k
        numer = numer * x
        If x <= k Then denom(1) = denom(1) * x
        If x <= n Then denom(2) = denom(2) * x
    Next x
    
    Combinations_Replacement = numer / (denom(1) * denom(2))
    
    Erase denom
    
End Function

 

[JenniferMurphy]

OK, thanks.

 

[JackDanIce]

You're welcome, last correction! (typo):

Function Combinations_Replacement(ByRef k As Long, ByRef n As Long) As Double
 
    Dim x               As Long
    Dim numer           As Long
    Dim denom(1 To 2)   As Long
    
    '(n-1+k)!/k!(n-1)!
    n = n - 1
    numer = 1: denom(1) = 1: denom(2) = 1
    
    For x = 1 To n + k
        numer = numer * x
        If x <= k Then denom(1) = denom(1) * x
        If x <= n Then denom(2) = denom(2) * x
    Next x
    
    Combinations_Replacement = numer / (denom(1) * denom(2))
    
    Erase denom
    
End Function