ARRANGEMENTS

[Xlambda]

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

=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.

 

[Xlambda]

T_CAF(a) Tool function calculating Combinations indexes, ( COMBINA ) using other Function AFUSBYROW.

=LAMBDA(a,UNIQUE(AFUSBYROW(a,,,1)))

LAMBDA 1.2.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	T_CAF(a) , alternative of T_CA(a) or T_CAN(a), using other Functions (AFUSBYROW) - filters only rows in ascending order, order is not important
2	( f,u,s are the filter, unique, sort, arguments of AFUSBYROW )
3
4	1. combinations with repetitions																2. combinations w/o repetitions							other functions
5	permutationa(4,3)				step 1 sort ascending by each row of a																			AFUSBYROW
6	n,4,c,3				f,u,omitted,s,1				step 2 unique(a')				step 3 single cell
7	=T_PA(4,3)		a		=AFUSBYROW(A8#,,,1)				=UNIQUE(E8#)				=T_CAF(A8#)				=T_PF(T_PA(4,3))				=T_CAF(Q8#)
8	1	1	1		1	1	1	a'	1	1	1		1	1	1		1	2	3		1	2	3
9	1	1	2		1	1	2		1	1	2		1	1	2		1	2	4		1	2	4
10	1	1	3		1	1	3		1	1	3		1	1	3		1	3	2		1	3	4
11	1	1	4		1	1	4		1	1	4		1	1	4		1	3	4		2	3	4
12	1	2	1		1	1	2		1	2	2		1	2	2		1	4	2
13	1	2	2		1	2	2		1	2	3		1	2	3		1	4	3
14	1	2	3		1	2	3		1	2	4		1	2	4		2	1	3		=T_PF(T_CAF(A8#))
15	1	2	4		1	2	4		1	3	3		1	3	3		2	1	4		1	2	3
16	1	3	1		1	1	3		1	3	4		1	3	4		2	3	1		1	2	4
17	1	3	2		1	2	3		1	4	4		1	4	4		2	3	4		1	3	4
18	1	3	3		1	3	3		2	2	2		2	2	2		2	4	1		2	3	4
19	1	3	4		1	3	4		2	2	3		2	2	3		2	4	3
20	1	4	1		1	1	4		2	2	4		2	2	4		3	1	2
21	1	4	2		1	2	4		2	3	3		2	3	3		3	1	4
22	1	4	3		1	3	4		2	3	4		2	3	4		3	2	1
23	1	4	4		1	4	4		2	4	4		2	4	4		3	2	4
24	2	1	1		1	1	2		3	3	3		3	3	3		3	4	1
25	2	1	2		1	2	2		3	3	4		3	3	4		3	4	2
26	2	1	3		1	2	3		3	4	4		3	4	4		4	1	2
27	2	1	4		1	2	4		4	4	4		4	4	4		4	1	3
28	2	2	1		1	2	2										4	2	1
29	2	2	2		2	2	2										4	2	3
30	2	2	3		2	2	3										4	3	1
31	2	2	4		2	2	4										4	3	2
32	2	3	1		1	2	3
33	2	3	2		2	2	3
34	2	3	3		2	3	3
35	2	3	4		2	3	4
36	2	4	1		1	2	4
37	2	4	2		2	2	4
38	2	4	3		2	3	4
39	2	4	4		2	4	4
40	3	1	1		1	1	3
41	3	1	2		1	2	3
42	3	1	3		1	3	3
43	3	1	4		1	3	4
44	3	2	1		1	2	3
45	3	2	2		2	2	3
46	3	2	3		2	3	3
47	3	2	4		2	3	4
48	3	3	1		1	3	3
49	3	3	2		2	3	3
50	3	3	3		3	3	3
51	3	3	4		3	3	4
52	3	4	1		1	3	4
53	3	4	2		2	3	4
54	3	4	3		3	3	4
55	3	4	4		3	4	4
56	4	1	1		1	1	4
57	4	1	2		1	2	4
58	4	1	3		1	3	4
59	4	1	4		1	4	4
60	4	2	1		1	2	4
61	4	2	2		2	2	4
62	4	2	3		2	3	4
63	4	2	4		2	4	4
64	4	3	1		1	3	4
65	4	3	2		2	3	4
66	4	3	3		3	3	4
67	4	3	4		3	4	4
68	4	4	1		1	4	4
69	4	4	2		2	4	4
70	4	4	3		3	4	4
71	4	4	4		4	4	4
72
ARRG np7

Cell Formulas
Range	Formula
A7,U14,Q7,U7,E7,I7,M7	A7	=FORMULATEXT(A8)
A8:C71	A8	=T_PA(4,3)
E8:G71	E8	=AFUSBYROW(A8#,,,1)
I8:K27	I8	=UNIQUE(E8#)
M8:O27	M8	=T_CAF(A8#)
Q8:S31	Q8	=T_PF(T_PA(4,3))
U8:W11	U8	=T_CAF(Q8#)
U15:W18	U15	=T_PF(T_CAF(A8#))
Dynamic array formulas.

 

[Xlambda]

LAMBDA 1.2.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Task: filtering only rows of an array that are in ascending order, general use, all 3 methods T_CA(a) , T_CAN(a) , T_CAF(a)
2
3							recursive						lambda helper						other functions
4	sample				a		=T_CA(A5:E12)						=T_CAN(A5:E12)						=T_CAF(A5:E12)
5	a	b	c	d	e		a	b	c	d	e		a	b	c	d	e		a	b	c	d	e
6	a	c	b	d	e		1	2	3	4	5		1	2	3	4	5		1	2	3	4	5
7	1	2	3	4	5		a	a	b	c	d		a	a	b	c	d		a	a	b	c	d
8	1	3	2	4	5		1	2	2	3	4		1	2	2	3	4		1	2	2	3	4
9	a	a	b	c	d		1	2	3	a	b		1	2	3	a	b		1	2	3	a	b
10	1	2	2	3	4
11	a	b	1	2	3
12	1	2	3	a	b
13
14	a without rows with dups
15	=T_P(A5:E12)						=T_CA(A16#)						=T_CAN(A16#)						=T_CAF(A16#)
16	a	b	c	d	e		a	b	c	d	e		a	b	c	d	e		a	b	c	d	e
17	a	c	b	d	e		1	2	3	4	5		1	2	3	4	5		1	2	3	4	5
18	1	2	3	4	5		1	2	3	a	b		1	2	3	a	b		1	2	3	a	b
19	1	3	2	4	5
20	a	b	1	2	3
21	1	2	3	a	b		from a directly
22
23							=T_CA(T_P(A5:E12))						=T_CAN(T_P(A5:E12))						=T_CAF(T_PF(A5:E12))
24							a	b	c	d	e		a	b	c	d	e		a	b	c	d	e
25							1	2	3	4	5		1	2	3	4	5		1	2	3	4	5
26							1	2	3	a	b		1	2	3	a	b		1	2	3	a	b
27
28							=T_P(T_CA(A5:E12))						=T_PN(T_CAN(A5:E12))						=T_PF(T_CAF(A5:E12))
29							a	b	c	d	e		a	b	c	d	e		a	b	c	d	e
30							1	2	3	4	5		1	2	3	4	5		1	2	3	4	5
31							1	2	3	a	b		1	2	3	a	b		1	2	3	a	b
32
ARRG np8

Cell Formulas
Range	Formula
G4,S28,M28,G28,S23,M23,G23,S15,M15,G15,A15,S4,M4	G4	=FORMULATEXT(G5)
G5:K9	G5	=T_CA(A5:E12)
M5:Q9	M5	=T_CAN(A5:E12)
S5:W9	S5	=T_CAF(A5:E12)
A16:E21	A16	=T_P(A5:E12)
G16:K18	G16	=T_CA(A16#)
M16:Q18	M16	=T_CAN(A16#)
S16:W18	S16	=T_CAF(A16#)
G24:K26	G24	=T_CA(T_P(A5:E12))
M24:Q26	M24	=T_CAN(T_P(A5:E12))
S24:W26	S24	=T_CAF(T_PF(A5:E12))
G29:K31	G29	=T_P(T_CA(A5:E12))
M29:Q31	M29	=T_PN(T_CAN(A5:E12))
S29:W31	S29	=T_PF(T_CAF(A5:E12))
Dynamic array formulas.

 

[Xlambda]

Regarding component functions, the only stone left unturned is T_PA(n,c), a simple and straight forward function using MOD.

LAMBDA 1.2.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	T_PA(n,c) step by step.
2	=LAMBDA(n,c,MOD(ROUNDUP(SEQUENCE(n^c)/n^(c-SEQUENCE(,c)),0)-1,n)+1)
3	n=4,c=3																			check
4		=SEQUENCE(4^3)		=SEQUENCE(,3)				=SEQUENCE(4^3)/4^(3-SEQUENCE(,3))				=ROUNDUP(H5#,0)				=MOD(L5#-1,4)+1				=T_PA(4,3)
5		1		1	2	3		0.0625	0.25	1		1	1	1		1	1	1		1	1	1
6		2						0.125	0.5	2		1	1	2		1	1	2		1	1	2
7		3		=3-SEQUENCE(,3)				0.1875	0.75	3		1	1	3		1	1	3		1	1	3
8		4		2	1	0		0.25	1	4		1	1	4		1	1	4		1	1	4
9		5						0.3125	1.25	5		1	2	5		1	2	1		1	2	1
10		6		=4^(3-SEQUENCE(,3))				0.375	1.5	6		1	2	6		1	2	2		1	2	2
11		7		16	4	1		0.4375	1.75	7		1	2	7		1	2	3		1	2	3
12		8						0.5	2	8		1	2	8		1	2	4		1	2	4
13		9						0.5625	2.25	9		1	3	9		1	3	1		1	3	1
14		10						0.625	2.5	10		1	3	10		1	3	2		1	3	2
15		11						0.6875	2.75	11		1	3	11		1	3	3		1	3	3
16		12						0.75	3	12		1	3	12		1	3	4		1	3	4
17		13						0.8125	3.25	13		1	4	13		1	4	1		1	4	1
18		14						0.875	3.5	14		1	4	14		1	4	2		1	4	2
19		15						0.9375	3.75	15		1	4	15		1	4	3		1	4	3
20		16						1	4	16		1	4	16		1	4	4		1	4	4
21		17						1.0625	4.25	17		2	5	17		2	1	1		2	1	1
22		18						1.125	4.5	18		2	5	18		2	1	2		2	1	2
23		19						1.1875	4.75	19		2	5	19		2	1	3		2	1	3
24		20						1.25	5	20		2	5	20		2	1	4		2	1	4
25		21						1.3125	5.25	21		2	6	21		2	2	1		2	2	1
26		22						1.375	5.5	22		2	6	22		2	2	2		2	2	2
27		23						1.4375	5.75	23		2	6	23		2	2	3		2	2	3
28		24						1.5	6	24		2	6	24		2	2	4		2	2	4
29		25						1.5625	6.25	25		2	7	25		2	3	1		2	3	1
30		26						1.625	6.5	26		2	7	26		2	3	2		2	3	2
31		27						1.6875	6.75	27		2	7	27		2	3	3		2	3	3
32		28						1.75	7	28		2	7	28		2	3	4		2	3	4
33		29						1.8125	7.25	29		2	8	29		2	4	1		2	4	1
34		30						1.875	7.5	30		2	8	30		2	4	2		2	4	2
35		31						1.9375	7.75	31		2	8	31		2	4	3		2	4	3
36		32						2	8	32		2	8	32		2	4	4		2	4	4
37		33						2.0625	8.25	33		3	9	33		3	1	1		3	1	1
38		34						2.125	8.5	34		3	9	34		3	1	2		3	1	2
39		35						2.1875	8.75	35		3	9	35		3	1	3		3	1	3
40		36						2.25	9	36		3	9	36		3	1	4		3	1	4
41		37						2.3125	9.25	37		3	10	37		3	2	1		3	2	1
42		38						2.375	9.5	38		3	10	38		3	2	2		3	2	2
43		39						2.4375	9.75	39		3	10	39		3	2	3		3	2	3
44		40						2.5	10	40		3	10	40		3	2	4		3	2	4
45		41						2.5625	10.25	41		3	11	41		3	3	1		3	3	1
46		42						2.625	10.5	42		3	11	42		3	3	2		3	3	2
47		43						2.6875	10.75	43		3	11	43		3	3	3		3	3	3
48		44						2.75	11	44		3	11	44		3	3	4		3	3	4
49		45						2.8125	11.25	45		3	12	45		3	4	1		3	4	1
50		46						2.875	11.5	46		3	12	46		3	4	2		3	4	2
51		47						2.9375	11.75	47		3	12	47		3	4	3		3	4	3
52		48						3	12	48		3	12	48		3	4	4		3	4	4
53		49						3.0625	12.25	49		4	13	49		4	1	1		4	1	1
54		50						3.125	12.5	50		4	13	50		4	1	2		4	1	2
55		51						3.1875	12.75	51		4	13	51		4	1	3		4	1	3
56		52						3.25	13	52		4	13	52		4	1	4		4	1	4
57		53						3.3125	13.25	53		4	14	53		4	2	1		4	2	1
58		54						3.375	13.5	54		4	14	54		4	2	2		4	2	2
59		55						3.4375	13.75	55		4	14	55		4	2	3		4	2	3
60		56						3.5	14	56		4	14	56		4	2	4		4	2	4
61		57						3.5625	14.25	57		4	15	57		4	3	1		4	3	1
62		58						3.625	14.5	58		4	15	58		4	3	2		4	3	2
63		59						3.6875	14.75	59		4	15	59		4	3	3		4	3	3
64		60						3.75	15	60		4	15	60		4	3	4		4	3	4
65		61						3.8125	15.25	61		4	16	61		4	4	1		4	4	1
66		62						3.875	15.5	62		4	16	62		4	4	2		4	4	2
67		63						3.9375	15.75	63		4	16	63		4	4	3		4	4	3
68		64						4	16	64		4	16	64		4	4	4		4	4	4
69
ARRG np9

Cell Formulas
Range	Formula
B4,D4,H4,L4,P4,T4,D10,D7	B4	=FORMULATEXT(B5)
B5:B68	B5	=SEQUENCE(4^3)
D5:F5	D5	=SEQUENCE(,3)
H5:J68	H5	=SEQUENCE(4^3)/4^(3-SEQUENCE(,3))
L5:N68	L5	=ROUNDUP(H5#,0)
P5:R68	P5	=MOD(L5#-1,4)+1
T5:V68	T5	=T_PA(4,3)
D8:F8	D8	=3-SEQUENCE(,3)
D11:F11	D11	=4^(3-SEQUENCE(,3))
Dynamic array formulas.

 

[Xlambda]

We have seen 3 design variants for tool lambdas, using recursion, lambda helper functions, and other custom-made function.
Chose as final variant the set of tool lambdas that use lambda helper functions.
T_PA(n,c)

=LAMBDA(n, c, MOD(ROUNDUP(SEQUENCE(n ^ c) / n ^ (c - SEQUENCE(, c)), 0) - 1, n) + 1)

T_P(a)

=LAMBDA(a,
    LET(c, COLUMNS(a),
        FILTER(a,BYROW(REDUCE(0, SEQUENCE(c), LAMBDA(v,i, v + (INDEX(a, , i) = a))),LAMBDA(x, SUM(x) = c)))
    )
)

T_CA(a)

=LAMBDA(a,
    LET(c, COLUMNS(a),
        FILTER(a,REDUCE(1,SEQUENCE(c - 1),LAMBDA(v,i, v * (INDEX(a, , i) <= INDEX(a, , i + 1)))))
    )
)

ARRANGEMENTS(a,t,c) Note: Only modification to the initial one, replaced AFLAT(a) with TOCOL(IF(a = "", NA(), a), 3)

=LAMBDA(a,t,c,
    IF(AND(t <> {"p","pa","c","ca"}),"check type",
        LET(k, MAX(1, c),x, TOCOL(IF(a = "", NA(), a), 3),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)))))
        )
    )
)

 

[Xlambda]

Example inspired from today's (8May22) ExcelIsFun YT Excel Statistical Analysis 36: Sampling & Sampling Distribution of Sample Means (Xbar)
Create the following table using ARRANGEMENTS (min 15:48)

Ch07-ESA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Pop Size = N =				7
2	Sample Size = n =				3
3	Number of Samples Possible:				35
4
5	Sales Rep	Sales		Sales Rep 1	Sales Rep 2	Sales Rep 3	Sales 1	Sales 2	Sales 3	Xbar	P(Sample)
6	Jo	185		Jo	Sioux	Chin	185	250	210	215	0.028571429
7	Sioux	250		Jo	Sioux	Sheliadawn	185	250	310	248.3333333	0.028571429
8	Chin	210		Jo	Sioux	Gigi	185	250	298	244.3333333	0.028571429
9	Sheliadawn	310		Jo	Sioux	Tyrone	185	250	402	279	0.028571429
10	Gigi	298		Jo	Sioux	Kip	185	250	370	268.3333333	0.028571429
11	Tyrone	402		Jo	Chin	Sheliadawn	185	210	310	235	0.028571429
12	Kip	370		Jo	Chin	Gigi	185	210	298	231	0.028571429
13				Jo	Chin	Tyrone	185	210	402	265.6666667	0.028571429
14				Jo	Chin	Kip	185	210	370	255	0.028571429
15				Jo	Sheliadawn	Gigi	185	310	298	264.3333333	0.028571429
16				Jo	Sheliadawn	Tyrone	185	310	402	299	0.028571429
17				Jo	Sheliadawn	Kip	185	310	370	288.3333333	0.028571429
18				Jo	Gigi	Tyrone	185	298	402	295	0.028571429
19				Jo	Gigi	Kip	185	298	370	284.3333333	0.028571429
20				Jo	Tyrone	Kip	185	402	370	319	0.028571429
21				Sioux	Chin	Sheliadawn	250	210	310	256.6666667	0.028571429
22				Sioux	Chin	Gigi	250	210	298	252.6666667	0.028571429
23				Sioux	Chin	Tyrone	250	210	402	287.3333333	0.028571429
24				Sioux	Chin	Kip	250	210	370	276.6666667	0.028571429
25				Sioux	Sheliadawn	Gigi	250	310	298	286	0.028571429
26				Sioux	Sheliadawn	Tyrone	250	310	402	320.6666667	0.028571429
27				Sioux	Sheliadawn	Kip	250	310	370	310	0.028571429
28				Sioux	Gigi	Tyrone	250	298	402	316.6666667	0.028571429
29				Sioux	Gigi	Kip	250	298	370	306	0.028571429
30				Sioux	Tyrone	Kip	250	402	370	340.6666667	0.028571429
31				Chin	Sheliadawn	Gigi	210	310	298	272.6666667	0.028571429
32				Chin	Sheliadawn	Tyrone	210	310	402	307.3333333	0.028571429
33				Chin	Sheliadawn	Kip	210	310	370	296.6666667	0.028571429
34				Chin	Gigi	Tyrone	210	298	402	303.3333333	0.028571429
35				Chin	Gigi	Kip	210	298	370	292.6666667	0.028571429
36				Chin	Tyrone	Kip	210	402	370	327.3333333	0.028571429
37				Sheliadawn	Gigi	Tyrone	310	298	402	336.6666667	0.028571429
38				Sheliadawn	Gigi	Kip	310	298	370	326	0.028571429
39				Sheliadawn	Tyrone	Kip	310	402	370	360.6666667	0.028571429
40				Gigi	Tyrone	Kip	298	402	370	356.6666667	0.028571429
41
All Possible Samples (an)

Cell Formulas
Range	Formula
E1	E1	=COUNTA(A6:A12)
E3	E3	=COMBIN(E1,E2)
G6:I40	G6	=XLOOKUP(D6:F40,A6:A12,B6:B12)
J6:J40	J6	=AVERAGE(G6:I6)
K6:K40	K6	=1/$E$3
Dynamic array formulas.

 

[Xlambda]

Ch07-ESA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Pop Size = N =				7
2	Sample Size = n =				3
3	Number of Samples Possible:				35			=MAP(D6#,LAMBDA(x,VLOOKUP(x,A6:B12,2,0)))
4								↓↓				=BYROW(H6#,LAMBDA(x,AVERAGE(x)))
5	Sales Rep	Sales		=ARRANGEMENTS(A6:A12,"c",E2)				↓↓				↓↓		=SEQUENCE(ROWS(D6#))^0/ROWS(D6#)
6	Jo	185		Jo	Sioux	Chin		185	250	210		215		0.028571429
7	Sioux	250		Jo	Sioux	Sheliadawn		185	250	310		248.3333333		0.028571429
8	Chin	210		Jo	Sioux	Gigi		185	250	298		244.3333333		0.028571429
9	Sheliadawn	310		Jo	Sioux	Tyrone		185	250	402		279		0.028571429
10	Gigi	298		Jo	Sioux	Kip		185	250	370		268.3333333		0.028571429
11	Tyrone	402		Jo	Chin	Sheliadawn		185	210	310		235		0.028571429
12	Kip	370		Jo	Chin	Gigi		185	210	298		231		0.028571429
13				Jo	Chin	Tyrone		185	210	402		265.6666667		0.028571429
14				Jo	Chin	Kip		185	210	370		255		0.028571429
15				Jo	Sheliadawn	Gigi		185	310	298		264.3333333		0.028571429
16				Jo	Sheliadawn	Tyrone		185	310	402		299		0.028571429
17				Jo	Sheliadawn	Kip		185	310	370		288.3333333		0.028571429
18				Jo	Gigi	Tyrone		185	298	402		295		0.028571429
19				Jo	Gigi	Kip		185	298	370		284.3333333		0.028571429
20				Jo	Tyrone	Kip		185	402	370		319		0.028571429
21				Sioux	Chin	Sheliadawn		250	210	310		256.6666667		0.028571429
22				Sioux	Chin	Gigi		250	210	298		252.6666667		0.028571429
23				Sioux	Chin	Tyrone		250	210	402		287.3333333		0.028571429
24				Sioux	Chin	Kip		250	210	370		276.6666667		0.028571429
25				Sioux	Sheliadawn	Gigi		250	310	298		286		0.028571429
26				Sioux	Sheliadawn	Tyrone		250	310	402		320.6666667		0.028571429
27				Sioux	Sheliadawn	Kip		250	310	370		310		0.028571429
28				Sioux	Gigi	Tyrone		250	298	402		316.6666667		0.028571429
29				Sioux	Gigi	Kip		250	298	370		306		0.028571429
30				Sioux	Tyrone	Kip		250	402	370		340.6666667		0.028571429
31				Chin	Sheliadawn	Gigi		210	310	298		272.6666667		0.028571429
32				Chin	Sheliadawn	Tyrone		210	310	402		307.3333333		0.028571429
33				Chin	Sheliadawn	Kip		210	310	370		296.6666667		0.028571429
34				Chin	Gigi	Tyrone		210	298	402		303.3333333		0.028571429
35				Chin	Gigi	Kip		210	298	370		292.6666667		0.028571429
36				Chin	Tyrone	Kip		210	402	370		327.3333333		0.028571429
37				Sheliadawn	Gigi	Tyrone		310	298	402		336.6666667		0.028571429
38				Sheliadawn	Gigi	Kip		310	298	370		326		0.028571429
39				Sheliadawn	Tyrone	Kip		310	402	370		360.6666667		0.028571429
40				Gigi	Tyrone	Kip		298	402	370		356.6666667		0.028571429
41
Sheet1

Cell Formulas
Range	Formula
E1	E1	=COUNTA(A6:A12)
E3	E3	=COMBIN(E1,E2)
H3	H3	=FORMULATEXT(H6)
L4	L4	=FORMULATEXT(L6)
D5,N5	D5	=FORMULATEXT(D6)
D6:F40	D6	=ARRANGEMENTS(A6:A12,"c",E2)
H6:J40	H6	=MAP(D6#,LAMBDA(x,VLOOKUP(x,A6:B12,2,0)))
L6:L40	L6	=BYROW(H6#,LAMBDA(x,AVERAGE(x)))
N6:N40	N6	=SEQUENCE(ROWS(D6#))^0/ROWS(D6#)
Dynamic array formulas.

 

[Xlambda]

Single cell formula in D6:

=LET(rp,A8:A14,sl,B8:B14,n,E2,
         a,ARRANGEMENTS(rp,"c",n),r,ROWS(a),
         b,MAP(a,LAMBDA(x,XLOOKUP(x,rp,sl))),
         c,BYROW(b,LAMBDA(x,AVERAGE(x))),
         d,SEQUENCE(r)^0/r,
         HSTACK(a,b,c,d)
 )

Ch07-ESA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Pop Size = N =				7
2	Sample Size = n =				3
3	Number of Samples Possible:				35
4
5	Sales Rep	Sales		Sales Rep 1	Sales Rep 2	Sales Rep 3	Sales 1	Sales 2	Sales 3	Xbar	P(Sample)
6	Jo	185		Jo	Sioux	Chin	185	250	210	215	0.028571429
7	Sioux	250		Jo	Sioux	Sheliadawn	185	250	310	248.3333333	0.028571429
8	Chin	210		Jo	Sioux	Gigi	185	250	298	244.3333333	0.028571429
9	Sheliadawn	310		Jo	Sioux	Tyrone	185	250	402	279	0.028571429
10	Gigi	298		Jo	Sioux	Kip	185	250	370	268.3333333	0.028571429
11	Tyrone	402		Jo	Chin	Sheliadawn	185	210	310	235	0.028571429
12	Kip	370		Jo	Chin	Gigi	185	210	298	231	0.028571429
13				Jo	Chin	Tyrone	185	210	402	265.6666667	0.028571429
14				Jo	Chin	Kip	185	210	370	255	0.028571429
15				Jo	Sheliadawn	Gigi	185	310	298	264.3333333	0.028571429
16				Jo	Sheliadawn	Tyrone	185	310	402	299	0.028571429
17				Jo	Sheliadawn	Kip	185	310	370	288.3333333	0.028571429
18				Jo	Gigi	Tyrone	185	298	402	295	0.028571429
19				Jo	Gigi	Kip	185	298	370	284.3333333	0.028571429
20				Jo	Tyrone	Kip	185	402	370	319	0.028571429
21				Sioux	Chin	Sheliadawn	250	210	310	256.6666667	0.028571429
22				Sioux	Chin	Gigi	250	210	298	252.6666667	0.028571429
23				Sioux	Chin	Tyrone	250	210	402	287.3333333	0.028571429
24				Sioux	Chin	Kip	250	210	370	276.6666667	0.028571429
25				Sioux	Sheliadawn	Gigi	250	310	298	286	0.028571429
26				Sioux	Sheliadawn	Tyrone	250	310	402	320.6666667	0.028571429
27				Sioux	Sheliadawn	Kip	250	310	370	310	0.028571429
28				Sioux	Gigi	Tyrone	250	298	402	316.6666667	0.028571429
29				Sioux	Gigi	Kip	250	298	370	306	0.028571429
30				Sioux	Tyrone	Kip	250	402	370	340.6666667	0.028571429
31				Chin	Sheliadawn	Gigi	210	310	298	272.6666667	0.028571429
32				Chin	Sheliadawn	Tyrone	210	310	402	307.3333333	0.028571429
33				Chin	Sheliadawn	Kip	210	310	370	296.6666667	0.028571429
34				Chin	Gigi	Tyrone	210	298	402	303.3333333	0.028571429
35				Chin	Gigi	Kip	210	298	370	292.6666667	0.028571429
36				Chin	Tyrone	Kip	210	402	370	327.3333333	0.028571429
37				Sheliadawn	Gigi	Tyrone	310	298	402	336.6666667	0.028571429
38				Sheliadawn	Gigi	Kip	310	298	370	326	0.028571429
39				Sheliadawn	Tyrone	Kip	310	402	370	360.6666667	0.028571429
40				Gigi	Tyrone	Kip	298	402	370	356.6666667	0.028571429
41
All Possible Samples (an) (2)

Cell Formulas
Range	Formula
E1	E1	=COUNTA(A6:A12)
E3	E3	=COMBIN(E1,E2)
D6:K40	D6	=LET(rp,A6:A12,sl,B6:B12,n,E2,a,ARRANGEMENTS(rp,"c",n),r,ROWS(a),b,MAP(a,LAMBDA(x,XLOOKUP(x,rp,sl))),c,BYROW(b,LAMBDA(x,AVERAGE(x))),d,SEQUENCE(r)^0/r,HSTACK(a,b,c,d))
Dynamic array formulas.

 

[Xlambda]

For the ones familiar with probabilities, ARRANGEMENTS can also be useful to create so-called sample points.
A deck of cards has face cards "F"(A,J,Q,K) and no face cards "NF".
Task: List all possible distributions of 5 card extractions considering only the ones that contain 2 face "F" cards.
Will use permutations with repetitions,"pa", n=2 (F,NF) , nr. chosen c=5 with FILTER.

Ch07-ESA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	a	F	NF
3
4		t,"pa",c,5						=BYROW(B6#,LAMBDA(x,SUM(--(x="f"))=2))
5		=ARRANGEMENTS(B2:C2,"pa",5)						↓↓		=FILTER(B6#,H6#)
6		F	F	F	F	F		FALSE		F	F	NF	NF	NF
7		F	F	F	F	NF		FALSE		F	NF	F	NF	NF
8		F	F	F	NF	F		FALSE		F	NF	NF	F	NF
9		F	F	F	NF	NF		FALSE		F	NF	NF	NF	F
10		F	F	NF	F	F		FALSE		NF	F	F	NF	NF
11		F	F	NF	F	NF		FALSE		NF	F	NF	F	NF
12		F	F	NF	NF	F		FALSE		NF	F	NF	NF	F
13		F	F	NF	NF	NF		TRUE		NF	NF	F	F	NF
14		F	NF	F	F	F		FALSE		NF	NF	F	NF	F
15		F	NF	F	F	NF		FALSE		NF	NF	NF	F	F
16		F	NF	F	NF	F		FALSE
17		F	NF	F	NF	NF		TRUE			how many?
18		F	NF	NF	F	F		FALSE			=ROWS(J6#)
19		F	NF	NF	F	NF		TRUE			10
20		F	NF	NF	NF	F		TRUE
21		F	NF	NF	NF	NF		FALSE		Because n<c (2<5) permutations or combinations w/o repetitions ("p" or "c") will not work
22		NF	F	F	F	F		FALSE
23		NF	F	F	F	NF		FALSE		=ARRANGEMENTS(B2:C2,"p",5)
24		NF	F	F	NF	F		FALSE		nr chosen>n !
25		NF	F	F	NF	NF		TRUE
26		NF	F	NF	F	F		FALSE		=ARRANGEMENTS(B2:C2,"c",5)
27		NF	F	NF	F	NF		TRUE		nr chosen>n !
28		NF	F	NF	NF	F		TRUE
29		NF	F	NF	NF	NF		FALSE
30		NF	NF	F	F	F		FALSE
31		NF	NF	F	F	NF		TRUE
32		NF	NF	F	NF	F		TRUE
33		NF	NF	F	NF	NF		FALSE
34		NF	NF	NF	F	F		TRUE
35		NF	NF	NF	F	NF		FALSE
36		NF	NF	NF	NF	F		FALSE
37		NF	NF	NF	NF	NF		FALSE
38
Sheet3

Cell Formulas
Range	Formula
H4	H4	=FORMULATEXT(H6)
B5,J26,J23,K18,J5	B5	=FORMULATEXT(B6)
B6:F37	B6	=ARRANGEMENTS(B2:C2,"pa",5)
H6:H37	H6	=BYROW(B6#,LAMBDA(x,SUM(--(x="f"))=2))
J6:N15	J6	=FILTER(B6#,H6#)
K19	K19	=ROWS(J6#)
J24	J24	=ARRANGEMENTS(B2:C2,"p",5)
J27	J27	=ARRANGEMENTS(B2:C2,"c",5)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H6:H37	Expression	=H6	text	NO
B6:F37	Expression	=$H6	text	NO

 

[Xlambda]

For this example, face cards "F"=(J,Q,K), "A" the aces, and the rest no face cards "NF".
Task: List all possible distributions of 6 card extractions considering only the ones that contain 2 face cards "F" and 1 Ace card "A".

Ch07-ESA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	a	A	F	NF
3									=BYROW(B5#,LAMBDA(x,AND(SUM(--(x="a"))=1,SUM(--(x="f"))=2)))
4		=ARRANGEMENTS(B2:D2,"pa",6)							↓↓							=FILTER(B5#,I5#)
5		A	A	A	A	A	A		FALSE		checking					A	F	F	NF	NF	NF		how many ?
6		A	A	A	A	A	F		FALSE		=PERMUTATIONA(3,6)					A	F	NF	F	NF	NF		=ROWS(P5#)
7		A	A	A	A	A	NF		FALSE		729					A	F	NF	NF	F	NF		60
8		A	A	A	A	F	A		FALSE							A	F	NF	NF	NF	F
9		A	A	A	A	F	F		FALSE		=ROWS(B5#)					A	NF	F	F	NF	NF
10		A	A	A	A	F	NF		FALSE		729					A	NF	F	NF	F	NF
11		A	A	A	A	NF	A		FALSE							A	NF	F	NF	NF	F
12		A	A	A	A	NF	F		FALSE							A	NF	NF	F	F	NF
13		A	A	A	A	NF	NF		FALSE							A	NF	NF	F	NF	F
14		A	A	A	F	A	A		FALSE							A	NF	NF	NF	F	F
15		A	A	A	F	A	F		FALSE							F	A	F	NF	NF	NF
16		A	A	A	F	A	NF		FALSE							F	A	NF	F	NF	NF
17		A	A	A	F	F	A		FALSE							F	A	NF	NF	F	NF
18		A	A	A	F	F	F		FALSE							F	A	NF	NF	NF	F
19		A	A	A	F	F	NF		FALSE							F	F	A	NF	NF	NF
20		A	A	A	F	NF	A		FALSE							F	F	NF	A	NF	NF
21		A	A	A	F	NF	F		FALSE							F	F	NF	NF	A	NF
22		A	A	A	F	NF	NF		FALSE							F	F	NF	NF	NF	A
23		A	A	A	NF	A	A		FALSE							F	NF	A	F	NF	NF
24		A	A	A	NF	A	F		FALSE							F	NF	A	NF	F	NF
25		A	A	A	NF	A	NF		FALSE							F	NF	A	NF	NF	F
26		A	A	A	NF	F	A		FALSE							F	NF	F	A	NF	NF
27		A	A	A	NF	F	F		FALSE							F	NF	F	NF	A	NF
28		A	A	A	NF	F	NF		FALSE							F	NF	F	NF	NF	A
29		A	A	A	NF	NF	A		FALSE							F	NF	NF	A	F	NF
30		A	A	A	NF	NF	F		FALSE							F	NF	NF	A	NF	F
31		A	A	A	NF	NF	NF		FALSE							F	NF	NF	F	A	NF
32		A	A	F	A	A	A		FALSE							F	NF	NF	F	NF	A
33		A	A	F	A	A	F		FALSE							F	NF	NF	NF	A	F
34		A	A	F	A	A	NF		FALSE							F	NF	NF	NF	F	A
35		A	A	F	A	F	A		FALSE							NF	A	F	F	NF	NF
36		A	A	F	A	F	F		FALSE							NF	A	F	NF	F	NF
37		A	A	F	A	F	NF		FALSE							NF	A	F	NF	NF	F
38		A	A	F	A	NF	A		FALSE							NF	A	NF	F	F	NF
39		A	A	F	A	NF	F		FALSE							NF	A	NF	F	NF	F
40		A	A	F	A	NF	NF		FALSE							NF	A	NF	NF	F	F
41		A	A	F	F	A	A		FALSE							NF	F	A	F	NF	NF
42		A	A	F	F	A	F		FALSE							NF	F	A	NF	F	NF
43		A	A	F	F	A	NF		FALSE							NF	F	A	NF	NF	F
44		A	A	F	F	F	A		FALSE							NF	F	F	A	NF	NF
45		A	A	F	F	F	F		FALSE							NF	F	F	NF	A	NF
46		A	A	F	F	F	NF		FALSE							NF	F	F	NF	NF	A
47		A	A	F	F	NF	A		FALSE							NF	F	NF	A	F	NF
48		A	A	F	F	NF	F		FALSE							NF	F	NF	A	NF	F
49		A	A	F	F	NF	NF		FALSE							NF	F	NF	F	A	NF
50		A	A	F	NF	A	A		FALSE							NF	F	NF	F	NF	A
51		A	A	F	NF	A	F		FALSE							NF	F	NF	NF	A	F
52		A	A	F	NF	A	NF		FALSE							NF	F	NF	NF	F	A
53		A	A	F	NF	F	A		FALSE							NF	NF	A	F	F	NF
54		A	A	F	NF	F	F		FALSE							NF	NF	A	F	NF	F
55		A	A	F	NF	F	NF		FALSE							NF	NF	A	NF	F	F
56		A	A	F	NF	NF	A		FALSE							NF	NF	F	A	F	NF
57		A	A	F	NF	NF	F		FALSE							NF	NF	F	A	NF	F
58		A	A	F	NF	NF	NF		FALSE							NF	NF	F	F	A	NF
59		A	A	NF	A	A	A		FALSE							NF	NF	F	F	NF	A
60		A	A	NF	A	A	F		FALSE							NF	NF	F	NF	A	F
61		A	A	NF	A	A	NF		FALSE							NF	NF	F	NF	F	A
62		A	A	NF	A	F	A		FALSE							NF	NF	NF	A	F	F
63		A	A	NF	A	F	F		FALSE							NF	NF	NF	F	A	F
64		A	A	NF	A	F	NF		FALSE		down to 729 rows					NF	NF	NF	F	F	A
65		A	A	NF	A	NF	A		FALSE		↓↓↓↓↓↓↓↓↓↓
Sheet4

Cell Formulas
Range	Formula
I3	I3	=FORMULATEXT(I5)
B4,K9,W6,K6,P4	B4	=FORMULATEXT(B5)
B5:G733	B5	=ARRANGEMENTS(B2:D2,"pa",6)
I5:I733	I5	=BYROW(B5#,LAMBDA(x,AND(SUM(--(x="a"))=1,SUM(--(x="f"))=2)))
P5:U64	P5	=FILTER(B5#,I5#)
K7	K7	=PERMUTATIONA(3,6)
W7	W7	=ROWS(P5#)
K10	K10	=ROWS(B5#)
Dynamic array formulas.

 

[Xlambda]

Sample point distributions for the same task, all 4 scenarios.
Task: Sample point distributions of throwing 3 dice that together sum 13.
1st scenario - repetitions are allowed, order is important (type argument t=pa)
2nd scenario - no repetitions, order is important (type argument t=p)
3rd scenario - repetitions are allowed, order not important (type argument t=ca)
4th scenario - no repetitions, order not important (type argument t=c)

ExcelIsFun Ch07-ESA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Task: Sample points of throwing 3 dice that togheter sum 13
2				1st scenario - repetitions are allowed, order is important (type argument t=pa)
3	=PERMUTATIONA(6,3)			=ARRANGEMENTS(SEQUENCE(6),"pa",3)
4	216			↓↓				=BYROW(D6#,LAMBDA(x,SUM(x)))
5	=ROWS(D6#)			↓↓				↓↓		=FILTER(D6#,H6#=13)				=UNICHAR(J6#+9855)
6	216			1	1	1		3		1	6	6		⚀	⚅	⚅		how many?
7				1	1	2		4		2	5	6		⚁	⚄	⚅		=ROWS(J6#)
8	=SEQUENCE(6,,9856)			1	1	3		5		2	6	5		⚁	⚅	⚄		21
9	↓↓	=UNICHAR(A10#)		1	1	4		6		3	4	6		⚂	⚃	⚅
10	9856	⚀		1	1	5		7		3	5	5		⚂	⚄	⚄
11	9857	⚁		1	1	6		8		3	6	4		⚂	⚅	⚃
12	9858	⚂		1	2	1		4		4	3	6		⚃	⚂	⚅
13	9859	⚃		1	2	2		5		4	4	5		⚃	⚃	⚄
14	9860	⚄		1	2	3		6		4	5	4		⚃	⚄	⚃
15	9861	⚅		1	2	4		7		4	6	3		⚃	⚅	⚂
16				1	2	5		8		5	2	6		⚄	⚁	⚅
17				1	2	6		9		5	3	5		⚄	⚂	⚄
18				1	3	1		5		5	4	4		⚄	⚃	⚃
19				1	3	2		6		5	5	3		⚄	⚄	⚂
20				1	3	3		7		5	6	2		⚄	⚅	⚁
21				1	3	4		8		6	1	6		⚅	⚀	⚅
22				1	3	5		9		6	2	5		⚅	⚁	⚄
23				1	3	6		10		6	3	4		⚅	⚂	⚃
24				1	4	1		6		6	4	3		⚅	⚃	⚂
25				1	4	2		7		6	5	2		⚅	⚄	⚁
26				1	4	3		8		6	6	1		⚅	⚅	⚀
27				1	4	4		9
28				1	4	5		10
29				1	4	6		11
30				1	5	1		7
31				1	5	2		8
32				1	5	3		9
33				1	5	4		10
34				1	5	5		11
35				1	5	6		12
36				1	6	1		8
37				1	6	2		9
38				1	6	3		10
39				1	6	4		11
40				1	6	5		12
41				1	6	6		13
42				2	1	1		4
43				2	1	2		5
44				2	1	3		6
45				2	1	4		7
46				2	1	5		8
47				2	1	6		9
48				2	2	1		5
49				2	2	2		6
50				2	2	3		7
51				2	2	4		8
52				2	2	5		9
53				2	2	6		10
54				2	3	1		6
55				2	3	2		7
56				2	3	3		8
57				2	3	4		9
58				2	3	5		10
59				2	3	6		11
60				2	4	1		7		down to 216 rows
61				2	4	2		8		↓↓↓↓↓↓↓↓↓↓
62				2	4	3		9
DICE 1

Cell Formulas
Range	Formula
A3,B9,R7,J5,N5,A5	A3	=FORMULATEXT(A4)
D3	D3	=FORMULATEXT(D6)
A4	A4	=PERMUTATIONA(6,3)
H4,A8	H4	=FORMULATEXT(H6)
A6	A6	=ROWS(D6#)
D6:F221	D6	=ARRANGEMENTS(SEQUENCE(6),"pa",3)
H6:H221	H6	=BYROW(D6#,LAMBDA(x,SUM(x)))
J6:L26	J6	=FILTER(D6#,H6#=13)
N6:P26	N6	=UNICHAR(J6#+9855)
R8	R8	=ROWS(J6#)
A10:A15	A10	=SEQUENCE(6,,9856)
B10:B15	B10	=UNICHAR(A10#)
Dynamic array formulas.