LAMBDA for combinatorics

[...] I am not a Frankenstein. I'm a FrankensTeam

In March 2013 together with my friends Kris - Krisztina Szabó and Gábor Madács we published an article entitled Combinatorics using Excel formulas.

It was a complete guide on how to develop combinatorics solutions with Excel formulas. In this guide you will find explanations of the cases analyzed, formulas (valid in every version of Excel) and much more.

The following is an addition to that document.

Given a set S of n objects we want to count and list the configurations that k objects taken from this set can assume.

Today I revised some of those formulas by adapting them to the new Excel functions. In particular, below are some LAMBDAs that you can use as:

=FunctionName(n,k)

Each is in two versions:

- LAMBDA 1 returns a vector of numbers (as many as there are possible configurations). Each number is made up of k characters, each of them is an object n of the set S.

 - LAMBDA 2 (which I called b) returns a matrix in which the number of columns is equal to k and the number of rows is equal to the number of solutions. Each element of the matrix represents an object n of the set S.

Setting n=5 the objects will be the numbers 1,2,3,4,5.

By setting k=3 the objects above will be combined in series of 3:

1,2,3 | 1,2,4 | 1,2,5 | ...

In all the cases that we are going to calculate we will always start from a vector of n^k elements. It is good to keep this in mind because even if the results will be a lower number of elements, n^k turns out to be the limit for the calculation in Excel. So n^k must be less than 2^20.

Sequences With Repetitions (k-tuples)

The number of hits can be calculated in two ways:

=n^k        

LAMBDA 1

SWR =LAMBDA(n,k,MMULT(1+MOD(INT(SEQUENCE(n^k,,0)/(n^SEQUENCE(,k,0))),n),10^SEQUENCE(k,,0)))        

=SWR(n,k)        

returns a vector of n^k numbers. Each number is made up of k digits and each digit is one of n elements

LAMBDA 2

SWRb 
=LAMBDA(n,k,1+MOD(INT(SEQUENCE(n^k,,0)/(n^SEQUENCE(,k,k-1,-1))),n))        

=SWRb (n,k)        

returns a matrix of k columns and n^k rows. Each value of the matrix is one of n elements

Sequences Without Repetition or Partial Permutation 

The number of hits can be calculated in two ways:

=FACT(n) / FACT(n-k)        

or:

=PERMUT(n,k)        

LAMBDA 1

SWoR 
=LAMBDA(n,k,LET(SWT,(1+MOD(INT(SEQUENCE(n^k,,0)/(n^SEQUENCE(,k,0))),n)), FILTER(MMULT(SWT,10^SEQUENCE(k,,0)), SUBSTITUTE(MMULT(10^SWT,SEQUENCE(k)^0),0,"")=REPT(1,k))))        

=SWoR (n,k)        

returns a vector of n!/(n-k)! numbers.

LAMBDA 2

SWoRb 
=LAMBDA(n,k,LET(SWT,(1+MOD(INT(SEQUENCE(n^k,,0)/(n^SEQUENCE(,k,k-1,-1))),n)), FILTER(SWT,SUBSTITUTE(MMULT(10^SWT,SEQUENCE(k)^0),0,"")=REPT(1,k))))        

=SWoRb (n,k)        

returns a matrix of k columns and n!/(n-k)! rows.

Combinations With Repetition

The number of hits can be calculated with:

=FACT(n+k-1) / ( FACT(n-1) * FACT(k) )        

LAMBDA 1

CWR 
=LAMBDA(n,k,LET(arr,SEQUENCE(n^k,,0), FILTER( MMULT(1+MOD(INT(arr/(n^SEQUENCE(,k,0))),n),1/10^(SEQUENCE(k)-k)), MMULT(--(MOD(INT(arr/n^SEQUENCE(,k-1,0)),n)<=MOD(INT(arr/n^SEQUENCE(,k-1)),n)),SEQUENCE(k-1)^0)=(k-1))))        

=CWR (n,k)        

returns a vector of (n+k+1)!/((n-1)!+k!) numbers.

LAMBDA 2

CWRb 
=LAMBDA(n,k,LET(arr,SEQUENCE(n^k,,0), FILTER( 1+MOD(INT(arr/(n^SEQUENCE(,k,0))),n), MMULT(--(MOD(INT(arr/n^SEQUENCE(,k-1,0)),n)<=MOD(INT(arr/n^SEQUENCE(,k-1)),n)),SEQUENCE(k-1)^0)=(k-1))))        

=CWRb (n,k)        

returns a matrix of k columns and (n+k+1)!/((n-1)!+k!) rows.

Combinations Without Repetition

The number of hits can be calculated with:

=COMBIN(n,k)        

LAMBDA 1

CWoR 
=LAMBDA(n,k,LET(arr,SEQUENCE(n^k,,0), FILTER( MMULT(1+MOD(INT(arr/(n^SEQUENCE(,k,0))),n),1/10^(SEQUENCE(k)-k)), MMULT(--(MOD(INT(arr/n^SEQUENCE(,k-1,0)),n)<MOD(INT(arr/n^SEQUENCE(,k-1)),n)),SEQUENCE(k-1)^0)=(k-1))))        

=CWoR (n,k)        

returns a vector of n!/(n-k)!*k! (where k<=n) numbers.

LAMBDA 2

CWoRb 
=LAMBDA(n,k,LET(arr,SEQUENCE(n^k,,0), FILTER( 1+MOD(INT(arr/(n^SEQUENCE(,k,0))),n), MMULT(--(MOD(INT(arr/n^SEQUENCE(,k-1,0)),n)<MOD(INT(arr/n^SEQUENCE(,k-1)),n)),SEQUENCE(k-1)^0)=(k-1))))        

=CWoRb (n,k)        

returns a matrix of k columns and n!/(n-k)!*k! rows.

Derangements

The number of hits can be calculated with:

=LET(n,B3,ABS(FACT(n)*SUM((-1^SEQUENCE(n+1))/FACT(SEQUENCE(n+1)-1))))        

LAMBDA 1

DRG 
=LAMBDA(n,LET(SWT,(1+MOD(INT(SEQUENCE(n^n,,0)/(n^SEQUENCE(,n,0))),n)), FILTER(MMULT(SWT,10^(n-SEQUENCE(n))), (SUBSTITUTE(MMULT(10^SWT,SEQUENCE(n)^0),0,"")=REPT(1,n))* (MMULT(--(SWT=SEQUENCE(n^n,,0,0)+SEQUENCE(,n)),SEQUENCE(n)^0)=0))))        

=DRG (n)        

returns a vector of numbers

LAMBDA 2

DRGb 
=LAMBDA(n,LET(SWT,(1+MOD(INT(SEQUENCE(n^n,,0)/(n^SEQUENCE(,n,0))),n)), FILTER(SWT, (SUBSTITUTE(MMULT(10^SWT,SEQUENCE(n)^0),0,"")=REPT(1,n))* (MMULT(--(SWT=SEQUENCE(n^n,,0,0)+SEQUENCE(,n)),SEQUENCE(n)^0)=0))))        

=DRGb (n)        

returns a matrix of n columns and a number of rows equal to the number of results.

You can download the file from the E90E50charts site

If you have alternative solutions please write them in the comments.

#challenge #excelchallenge #excel #combinatorics