Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 832
- Office Version
- 365
- Platform
- Windows
ARRANGEMENTS Extracts all permutations or combinations, with or without repetitions, of all elements of an array, by a number chosen.
Study inspired by latest MrExcel's YT (15aug21) Excel All Combinations Using Power Query - 2424
This is the first draft I came with, of course, the recursion way deserves first place. There are 2 other approaches, one using AFUSBYROW/BYCOL functions, and the other using new functions.
Calls 3 tool lambdas, 1 non recursive and 2 recursive (no risk to reach limitations, recursion takes place on the "short" direction, the columns one) . The recursive ones were created with ease using the ARF recursive kits concepts. Also, both recursive ones can be used as standalone lambdas, T_P is filtering an array by rows with no dups, and T_CA is filtering an array by rows that are in ascending order. The challenge was to do this by columns iterations and not by rows iteration (too many).
For ARRANGEMENTS function we use them to create the index patterns for extracting array's elements as are required. T_PA (non-recursive) creates the index pattern for permutations with repetitions, Once we have that, T_P (recursive) creates the index pattern for permutations without repetitions T_P(T_PA), T_CA creates the patterns for combinations with repetitions T_CA(T_PA), and for combinations without repetitions we use the same T_CA but this time, as input, we use T_P, T_CA(T_P). Calculation time for arrays having 1M rows 1 to 3 seconds.
T_PA(n,c)=LAMBDA(n,c,MOD(ROUNDUP(SEQUENCE(n^c)/n^(c-SEQUENCE(,c)),0)-1,n)+1) where n: number ; c: number chosen
T_P(a,[ai],[ i ] )=LAMBDA(a,[ai],[ i ],LET(n,COLUMNS(a),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,FILTER(a,MMULT(ai,SEQUENCE( n)^0)=n),T_P(a,ai+(x=a),j-1)))) !! recursive !!
T_CA(a,[ai],[ i ])=LAMBDA(a,[ai],[ i ],LET(n,COLUMNS(a),j,IF(i="",1,i),aj,IF(ai="",1,ai),x,INDEX(a,,j),IF(j=n,FILTER(a,aj),T_CA(a,aj*(x<=INDEX(a,,j+1)),j+1)))) !! recursive !! where a:array, ai,i,omitted
Study inspired by latest MrExcel's YT (15aug21) Excel All Combinations Using Power Query - 2424
This is the first draft I came with, of course, the recursion way deserves first place. There are 2 other approaches, one using AFUSBYROW/BYCOL functions, and the other using new functions.
Calls 3 tool lambdas, 1 non recursive and 2 recursive (no risk to reach limitations, recursion takes place on the "short" direction, the columns one) . The recursive ones were created with ease using the ARF recursive kits concepts. Also, both recursive ones can be used as standalone lambdas, T_P is filtering an array by rows with no dups, and T_CA is filtering an array by rows that are in ascending order. The challenge was to do this by columns iterations and not by rows iteration (too many).
For ARRANGEMENTS function we use them to create the index patterns for extracting array's elements as are required. T_PA (non-recursive) creates the index pattern for permutations with repetitions, Once we have that, T_P (recursive) creates the index pattern for permutations without repetitions T_P(T_PA), T_CA creates the patterns for combinations with repetitions T_CA(T_PA), and for combinations without repetitions we use the same T_CA but this time, as input, we use T_P, T_CA(T_P). Calculation time for arrays having 1M rows 1 to 3 seconds.
T_PA(n,c)=LAMBDA(n,c,MOD(ROUNDUP(SEQUENCE(n^c)/n^(c-SEQUENCE(,c)),0)-1,n)+1) where n: number ; c: number chosen
T_P(a,[ai],[ i ] )=LAMBDA(a,[ai],[ i ],LET(n,COLUMNS(a),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,FILTER(a,MMULT(ai,SEQUENCE( n)^0)=n),T_P(a,ai+(x=a),j-1)))) !! recursive !!
T_CA(a,[ai],[ i ])=LAMBDA(a,[ai],[ i ],LET(n,COLUMNS(a),j,IF(i="",1,i),aj,IF(ai="",1,ai),x,INDEX(a,,j),IF(j=n,FILTER(a,aj),T_CA(a,aj*(x<=INDEX(a,,j+1)),j+1)))) !! recursive !! where a:array, ai,i,omitted
Excel Formula:
=LAMBDA(a,t,c,
IF(AND(t<>{"p","pa","c","ca"}),"check type",
LET(k,MAX(1,c),x,AFLAT(a),n,ROWS(x),IF(AND(OR(t={"p","c"}),k>n),"nr chosen>n !",LET(y,T_PA(n,k),
SWITCH(t,"pa",INDEX(x,y),"p",INDEX(x,T_P(y)),"ca",INDEX(x,T_CA(y)),"c",LET(z,T_P(y),w,T_CA(z),INDEX(x,w))))))
)
)| LAMBDA 1.1.1.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | Introduction: Combinatorics Excel functions: | ||||||||||||||||
| 2 | 1,) PERMUTATIONA Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects. | ||||||||||||||||
| 3 | - PERMUTATIONA(number, number-chosen); "nc" or "c" (number chosen) can be >=n (number of elements/objects) ; order is important; PA=n^nc | ||||||||||||||||
| 4 | 2.) PERMUT Returns the number of permutations for a given number of objects (no repetitions) that can be selected from the total objects. | ||||||||||||||||
| 5 | - PERMUT(number, number_chosen); if nc>n returns #NUM! error; also called arrangements; order is important ;P=n!/(n-nc)! | ||||||||||||||||
| 6 | 3.) COMBINA Returns the number of combinations (with repetitions) for a given number of items. | ||||||||||||||||
| 7 | - COMBINA(number, number_chosen) ; nc can be > n; order is not important; CA=(n+nc-1)!/(nc!*(n-1)!) | ||||||||||||||||
| 8 | 4.) COMBIN Returns the number of combinations (no repetitions) for a given number of items. | ||||||||||||||||
| 9 | - COMBINA(number, number_chosen) ; nc can be > n; order is not important; C=P/nc! or C=n!/(nc!*(n-nc)!) | ||||||||||||||||
| 10 | What ARRANGEMENTS does is "printing" or extracting all this numbers of permutations or combinations, given the object array "a" and the number_chosen "c", | ||||||||||||||||
| 11 | for all types "t" above : "pa" for 1.) , "p" for 2.) , "ca" for 3.) ,"c" for 4.) | ||||||||||||||||
| 12 | |||||||||||||||||
| 13 | input n: | 7 | Table of values returned by all functions for an array of n objects ,nc [1,10] | ||||||||||||||
| 14 | |||||||||||||||||
| 15 | function\nc | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ||||||
| 16 | 1,) PERMUTATIONA | 7 | 49 | 343 | 2401 | 16807 | 117649 | 823543 | 5764801 | 40353607 | 282475249 | ||||||
| 17 | 2,) PERMUT | 7 | 42 | 210 | 840 | 2520 | 5040 | 5040 | #NUM! | #NUM! | #NUM! | ||||||
| 18 | 3.) COMBINA | 7 | 28 | 84 | 210 | 462 | 924 | 1716 | 3003 | 5005 | 8008 | ||||||
| 19 | 4.) COMBIN | 7 | 21 | 35 | 35 | 21 | 7 | 1 | #NUM! | #NUM! | #NUM! | ||||||
| 20 | |||||||||||||||||
| 21 | Note: | It is easy to run out of "printing" real estate in an excel spreadsheet for small arrays of objects | |||||||||||||||
| 22 | |||||||||||||||||
ARR post 1 | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C15:L15 | C15 | =SEQUENCE(,10) |
| C16:L16 | C16 | =PERMUTATIONA(C13,C15#) |
| C17:L17 | C17 | =PERMUT(C13,C15#) |
| C18:L18 | C18 | =COMBINA(C13,C15#) |
| C19:L19 | C19 | =COMBIN(C13,C15#) |
| Dynamic array formulas. | ||
Upvote
0
