ARF

[Xlambda]

ARF !! recursive !! DIY Array Recursive Function kit. Idea triggered by an ExcelIsFun YT video posted yesterday EMT1744 (different topic) . Other functions on minisheet AFLAT.
Other functions, never defined before, can be copied directly from minisheet APP and ASU, they follow same syntax as the "kit".
If a function (F) (build in or custom made), applied to an array (a), returns a single value or a horizontal 1D array (v), F(a)=v, then, ARF((a1,a2,…an),,)={F(a1);F(a2);…;F(an)}={v1;v2…;vn}
ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))
a: (a1,a2,…,an), non-adjacent ranges on same sheet, enclosed in parentheses like in 2nd syntax of INDEX function INDEX(reference, row_num, [column_num], [area_num])
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
If F has one or more arguments F(a,k) : (I will cover more complex examples as soon as I will have some spare time)
ARF(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,k,IF(s=j,F(x,k),ai),j-1))))

AGG study.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Base Case example (simplest case scenario): append arrays (single cell , or 1D horizontal arrays)
2	Note: We don't need more functionality since the functions will return single values or horizontal 1D arrays, keeping the kits construction as simple as possible
3	To append 2D arrays we already have					APPENDNHV
4	Writing the recursive function following the syntax draft, function name, let's define APP:
5	APP(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,APP(a,IF(s=j,x,ai),j-1))))
6	The appending "engine" functionality is extremely simple IF(s=j,x,ai)
7	Is equivalent with this :
8	=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))
9	a
10	b		a1				=APP((C11:D11,C14:E14,C17:D17),,)
11	2		a	2			a	2	#N/A
12							b	3	4
13			a2				1	2	#N/A
14			b	3	4
15
16			a3
17			1	2
18
19	General Case example: let's consider a more complex F(x) that sorts in ascending order the unique elements of an array
20		a1
21		a	2	3			=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))
22		x	w	2			2	3	a	t	w	x
23		t	x	a
24
25	so F(x) will be F(x)=TRANSPOSE(SORT(UNIQUE(AFLAT(x))))
26	Now let's define our specific recursive function (ASU) using the kit syntax
27	ASU(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ASU(a,IF(s=j,TRANSPOSE(SORT(UNIQUE(AFLAT(x)))),ai),j-1))))
28		a2
29		a	2	-1			=ASU((B21:D23,B29:D33,B36:C37),,)
30		q	a	c			2	3	a	t	w	x
31		d	c	2			-1	2	3	a	c	d	q
32		-1	3	-1			2	q
33		2	d	d
34
35		a3					Other function on minisheet
36		q	2				AFLAT
37		2	q
38
39	This proves the functionality of the kit, whatever function we can use to calculate an array and return a single cell result or 1D horiz array, can be done recursively to many arrays using the kit.
40	It will be nice to see others function creations posted here!!!
41
ARF post

Cell Formulas
Range	Formula
A8,G29,G21,G10	A8	=FORMULATEXT(A9)
A9:A11	A9	=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))
G11:I13	G11	=APP((C11:D11,C14:E14,C17:D17),,)
G22:L22	G22	=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))
G30:M32	G30	=ASU((B21:D23,B29:D33,B36:C37),,)
Dynamic array formulas.

 

[Xlambda]

More examples will follow .... increasing in complexity...but with help of ARF kit template....everything is simple and straight forward

AGG study.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Task 1: Extract the top k largest unique values of each array of an array "range" (no duplicates)
2
3	sample		step 1: study and create our custom made F(x,k) for a single array using LARGE
4	1	LARGE does not exclude duplicates					To exclude duplicates we need UNIQUE
5	2		=LARGE(A4:A10,SEQUENCE(3))				=LARGE(UNIQUE(A4:A10),SEQUENCE(3))
6	3		5				5
7	3		4				4
8	4		4				3
9	4
10	5		2D sample				=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(3))
11			a	4			5
12			3	4			4		since we need horizontal arrays, SEQUENCE(,3) will do the trick
13			3				3		=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(,3))
14			b	5					5	4	3
15			4	#N/A					our F(x,k)=LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k))
16			(mixed data,blanks,numeric,text,errors)
17			(AFLAT filters everything)
18
19	step 2: replace F(x,k) in the ARF kit template with LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k)) and define the new function
20	(also always check that all the arguments trough the syntax are consistent, no variable names duplicates..etc)
21	ARF function kit: ARF(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,k,IF(s=j,F(x,k),ai),j-1))))
22	Now we can define our specific recursive function for the task: ALARGE
23	ALARGE(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ALARGE(a,k,IF(s=j,LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k)),ai),j-1))))
24
25		a1						=ALARGE((B26:D29,B31:E33,B35:E39),2,,)
26		5	3	6			k,2	7	6					Imp Note:
27		4	2	g				5	4					A recursive function will work fine also for single arrays
28		d	5	6				9	8					=ALARGE(B35:E39,5,,)
29		3	k	7										9	8	7	6	5
30		a2						=ALARGE((B26:D29,B31:E33,B35:E39),3,,)
31		w	2	3	5		k,3	7	6	5
32		4	3	2	-1			5	4	3
33		0	2	z	x			9	8	7
34		a3
35		7	b	5	y			=ALARGE((B26:D29,B31:E33,B35:E39),4,,)
36		w	6	5	9		k,4	7	6	5	4
37		9	7	5	8			5	4	3	2
38		7	d	4	8			9	8	7	6
39		2	q	w	k
40
ARF post 2

Cell Formulas
Range	Formula
C5,G5,H35,H30,N28,H25,I13,G10	C5	=FORMULATEXT(C6)
C6:C8	C6	=LARGE(A4:A10,SEQUENCE(3))
G6:G8	G6	=LARGE(UNIQUE(A4:A10),SEQUENCE(3))
G11:G13	G11	=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(3))
I14:K14	I14	=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(,3))
D15	D15	=NA()
H26:I28	H26	=ALARGE((B26:D29,B31:E33,B35:E39),2,,)
N29:R29	N29	=ALARGE(B35:E39,5,,)
H31:J33	H31	=ALARGE((B26:D29,B31:E33,B35:E39),3,,)
H36:K38	H36	=ALARGE((B26:D29,B31:E33,B35:E39),4,,)
Dynamic array formulas.

 

[Xlambda]

AGG study.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	Z
1	Task 2: Integrate AGGREGATE function in a recursive function , for an array "range" (fn argument 1-13) (no k argument)
2	will use ARF function kit template:
3	ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))
4	Let's define ARF=AGG for F(x)=F(fn,o,x)=AGGREGATE(fn,o,x)
5	paying attention to the argument distribution consistency trough the function will get to:
6	AGG(a,fn,o,ai,i)=LAMBDA(a,fn,o,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,AGG(a,fn,o,IF(s=j,AGGREGATE(fn,o,x),ai),j-1))))
7	a1				a2					a3					a4						AGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )
8	5	3	6		w	2	3	5		7	b	5	y		t	-3	y	h	-2		function_num arg.			options argument
9	4	2	g		4	3	2	-1		w	6	-1	-1		h	4	l	5	4		↓	function		↓	values to be ignored
10	d	5	6		0	2	z	x		-2	2.5	-5	8		5	h	1	-3	3		1	Average		0	Ignore nested Subtotal & Aggregate functions
11	3	k	7							3	d	4	-3		3	1	-2	5	k		2	Count		1	Ignore hidden rows and nested Subtotal & Aggregate functions
12										2	q	w	k								3	Counta		2	Ignore error values and nested Subtotal & Aggregate functions
13																					4	Max		3	Ignore hidden rows, error values and nested Subtotal & Aggregate functions
14				o=6																	5	Min		4	Ignore nothing
15			fn	1	2	3	4	5	6	7	8	9	10	11	12	13					6	Product		5	Ignore hidden rows
16				4.6	9	12	7	2	453600	1.7	1.6	41	2.8	2.5	5	5					7	Stdev.S		6	Ignore error values
17				2.2	9	12	5	-1	0	1.9	1.7	20	3.4	3.1	2	2					8	Stdev.P		7	Ignore hidden rows and error values
18				2	13	20	8	-5	-3024000	4.1	3.9	26	17	15	2.5	-1					9	Sum
19				1.6	13	20	5	-3	648000	3.2	3	21	9.9	9.2	3	5					10	Var.S
20				=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),D15,6,,)																	11	Var.P
21																					12	Median
22				single cell formula for all fn																	13	Mode.Sngl
23				=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),SEQUENCE(,13),6,,)																	14	Large			fn 14-19 with k argument, next post, creating AGGK recursive function
24				4.6	9	12	7	2	453600	1.7	1.6	41	2.8	2.5	5	5					15	Small
25				2.2	9	12	5	-1	0	1.9	1.7	20	3.4	3.1	2	2					16	Percentile.Inc
26				2	13	20	8	-5	-3024000	4.1	3.9	26	17	15	2.5	-1					17	Quartile.Inc
27				1.6	13	20	5	-3	648000	3.2	3	21	9.9	9.2	3	5					18	Percentile.Exc
28																					19	Quartile.Exc
29				if we want only min,max and sum, in a single cell formula fn={5,4,9}
30				=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),{5,4,9},6,,)
31				2	7	41
32				-1	5	20
33				-5	8	26
34				-3	5	21
35
AGG post

Cell Formulas
Range	Formula
D16:P19	D16	=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),D15,6,,)
D20	D20	=FORMULATEXT(D16)
D23,D30	D23	=FORMULATEXT(D24)
D24:P27	D24	=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),SEQUENCE(,13),6,,)
D31:F34	D31	=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),{5,4,9},6,,)
Dynamic array formulas.

 

[Xlambda]

AGG study.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	Z	AA	AB	AC	AD
1	Task 3: Integrate AGGREGATE function in a recursive function , for an array "range" (fn argument 14-19) (with k argument)
2	will use ARF function kit template:
3	ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))
4	Let's define ARF=AGGK for F(x)=F(fn,o,x,k)=AGGREGATE(fn,o,x,k)
5	paying attention to the argument distribution consistency trough the function will get to:
6	AGGK(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,AGGK(a,fn,o,k,IF(s=j,AGGREGATE(fn,o,x,k),ai),j-1))))
7	a1				a2					a3					a4						AGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )
8	5	3	6		w	2	3	5		7	b	5	y		t	-3	y	h	-2		function_num arg.			options argument
9	4	2	g		4	3	2	-1		w	6	-1	-1		h	4	l	5	4		↓	function		↓	values to be ignored
10	d	5	6		0	2	z	x		-2	2.5	-5	8		5	h	1	-3	3		1	Average		0	Ignore nested Subtotal & Aggregate functions
11	3	k	7							3	d	4	-3		3	1	-2	5	k		2	Count		1	Ignore hidden rows and nested Subtotal & Aggregate functions
12										2	q	w	k								3	Counta		2	Ignore error values and nested Subtotal & Aggregate functions
13	o=6																				4	Max		3	Ignore hidden rows, error values and nested Subtotal & Aggregate functions
14	k={1,3}			k={1,2}			k={0,0.2,0.7,1}					k={0,1,2,3,4}									5	Min		4	Ignore nothing
15	fn=14			fn=15			fn=16					fn=17									6	Product		5	Ignore hidden rows
16	7	6		2	3		2	3	5.6	7		2	3	5	6	7					7	Stdev.S		6	Ignore error values
17	5	3		-1	0		-1	1.2	3	5		-1	2	2	3	5					8	Stdev.P		7	Ignore hidden rows and error values
18	8	6		-5	-3		-5	-1.6	4.4	8		-5	-1	2.5	5	8					9	Sum
19	5	5		-3	-3		-3	-2	4	5		-3	-2	3	4	5					10	Var.S
20																					11	Var.P
21							k={0.1,0.2,0.7,0.9}					k={1,2,3}									12	Median			The functions that require argument k
22							fn=18					fn=19									13	Mode.Sngl			function	meaning of k
23							2	3	6	7		3	5	6							14	Large		14	Large	Return the k'th largest value
24							-1	0	3	5		1	2	3.5							15	Small		15	Small	Return the k'th smallest value
25							-4.2	-2.2	4.8	7.6		-1.5	2.5	5.5							16	Percentile.Inc		16	Percentile.Inc	Return the k'th percentile	0<=k<=1
26							-3	-2.2	4	5		-2	3	4.5							17	Quartile.Inc		17	Quartile.Inc	Return the k'th quartile	0<=k<=4(<5)
27																					18	Percentile.Exc		18	Percentile.Exc	Return the k'th percentile	0<k<1
28				all FORMULATEXT syntax=AGGK((a1,a2,a3,a4),fn,o,k,,)																	19	Quartile.Exc		19	Quartile.Exc	Return the k'th quartile	1<=k<=3(<4)
29
AGGK post

Cell Formulas
Range	Formula
A16:B19	A16	=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),14,6,{1,3},,)
D16:E19	D16	=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),15,6,{1,2},,)
G16:J19	G16	=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),16,6,{0,0.2,0.7,1},,)
L16:P19	L16	=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),17,6,{0,1,2,3,4},,)
G23:J26	G23	=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),18,6,{0.1,0.2,0.7,0.9},,)
L23:N26	L23	=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),19,6,{1,2,3},,)
Dynamic array formulas.

 

[Xlambda]

AGG study.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	a1						Task 4 : a. Extract 2nd smallest and 2nd largest of each array of an array "range"
2	5	3	6				o=6,fn={14,15},k=2
3	4	2	g				=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15},6,2,,)
4	d	5	6				6	3
5	3	k	7				4	0		b. Extract Percentile.Inc and Percentile.Exc values for 10 and 90% , for each array of an array range
6	a2						7	-3		o=6,fn={16,18},k=0.1
7	w	2	3	5			5	-3		=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.1,,)
8	4	3	2	-1						2.8	2
9	0	2	z	x						-0.2	-1		o=6,fn={16,18},k=0.9
10	a3									-2.8	-4.2		=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.9,,)
11	7	b	5	y						-2.8	-3		6.2	7
12	w	6	-1	-1									4.2	5
13	-2	2.5	-5	8									6.8	7.6
14	3	d	4	-3									5	5
15	2	q	w	k
16	a4									c. Extract 1st and 3rd Quartile.Inc and Quartile.Exc values for each array of an array "range"
17	t	-3	y	h	-2					o=6,fn={17,19},k=1
18	h	4	l	5	4					=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,1,,)
19	5	h	1	-3	3					3	3
20	3	1	-2	5	k					2	1		o=6,fn={17,19},k=3
21										-1	-1.5		=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,3,,)
22										-2	-2		6	6
23													3	3.5
24													5	5.5
25													4	4.5
26
AGGK post 2

Cell Formulas
Range	Formula
G3,M21,J18,M10,J7	G3	=FORMULATEXT(G4)
G4:H7	G4	=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15},6,2,,)
J8:K11	J8	=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.1,,)
M11:N14	M11	=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.9,,)
J19:K22	J19	=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,1,,)
M22:N25	M22	=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,3,,)
Dynamic array formulas.

 

[Xlambda]

AGG study.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	a1						Task 5: All task 4 ops in a single cell
2	5	3	6
3	4	2	g				o=6,fn={14,15,16,18,16,18,17,19,17,19},k={2,2,0.1,0.1,0.9,0.9,1,1,3,3}
4	d	5	6
5	3	k	7				=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15,16,18,16,18,17,19,17,19},6,{2,2,0.1,0.1,0.9,0.9,1,1,3,3},,)
6	a2						6	3	2.8	2	6.2	7	3	3	6	6
7	w	2	3	5			4	0	-0.2	-1	4.2	5	2	1	3	3.5
8	4	3	2	-1			7	-3	-2.8	-4.2	6.8	7.6	-1	-1.5	5	5.5
9	0	2	z	x			5	-3	-2.8	-3	5	5	-2	-2	4	4.5
10	a3
11	7	b	5	y
12	w	6	-1	-1
13	-2	2.5	-5	8
14	3	d	4	-3
15	2	q	w	k
16	a4
17	t	-3	y	h	-2
18	h	4	l	5	4
19	5	h	1	-3	3
20	3	1	-2	5	k
21
AGGK post 3

Cell Formulas
Range	Formula
G5	G5	=FORMULATEXT(G6)
G6:P9	G6	=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15,16,18,16,18,17,19,17,19},6,{2,2,0.1,0.1,0.9,0.9,1,1,3,3},,)
Dynamic array formulas.

 

[Xlambda]

AGG study.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	a1						Task 6: Function_num argument 13 of AGGREGATE function is for MODE.SNGL
2	5	3	6				Let's create a recursive function that is able to calculate MODE.MULT for each array of an array "range"
3	4	2	g				Note: MODE.MULT is not among the functions covered by AGGREGATE because it can deliver an array result, but our function kit design can handle this with ease, so we will cover it.
4	d	5	6				step 1:					=MODE.MULT(A2:C5)			=TRANSPOSE(MODE.MULT(A2:C5))
5	3	k	7				Identify a formula F(x) for a single array					5			5	3	6
6	a2											3
7	w	2	3	5								6
8	4	3	2	-1
9	0	-1	z	x			since ARF formula kit template is designed to handle 1D horiz arrays, our F(x) will be:
10	a3						F(x)=TRANSPOSE(MODE.MULT(x))
11	7	b	5	y
12	w	6	-1	-1			step 2:
13	-2	2.5	-5	8			define new recursive function AMM, using the kit template
14	3	d	4	-3			ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))
15	6	q	w	k
16	a4						AMM(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,AMM(a,IF(s=j,TRANSPOSE(MODE.MULT(x)),ai),j-1))))
17	t	-3	y	h	-2		=AMM((A2:C5,A7:D9,A11:D15,A17:E20),,)
18	h	4	l	5	4		5	3	6
19	5	h	1	-3	3		2	3	-1
20	3	1	-2	5	k		6	-1	#N/A
21							5	5	#N/A
22
23							To handle the NA error , since the result vector carrier is ai, we modify in formula ai with IFERROR(ai,"") (after IF(j=0,ai,AMM(…. )
24							AMM(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),AMM(a,IF(s=j,TRANSPOSE(MODE.MULT(x)),ai),j-1))))
25							=AMMnoER((A2:C5,A7:D9,A11:D15,A17:E20),,)
26							5	3	6
27							2	3	-1		Note: To visualize the 2 behaviors at once , with errors and no errors, we needed 2 dif functions, that’s why we have AMMnoER
28							6	-1
29							5	5
30
AMM

Cell Formulas
Range	Formula
L4,O4,G25,G17	L4	=FORMULATEXT(L5)
L5:L7	L5	=MODE.MULT(A2:C5)
O5:Q5	O5	=TRANSPOSE(MODE.MULT(A2:C5))
G18:I21	G18	=AMM((A2:C5,A7:D9,A11:D15,A17:E20),,)
G26:I29	G26	=AMMnoER((A2:C5,A7:D9,A11:D15,A17:E20),,)
Dynamic array formulas.

 

[Xlambda]

Task 7: Wrap up all AGGREGATE related functions (AGG,AGGK,AMM) functionality in "all in one" tool function T_AGG(a,fn,o,k,ai,i) !! recursive !!
So far, is the first explicit function here based on ARF kit template apart of all intermediate "study" ones. The possibilities are limitless . Choose AGGREGATE functionality because really is a function that does a lot, and attach to it recursive array "range" versatility could be an useful tool. Hope that nobody will hate me for so many posts, it's only a fine journey that I think, deserves to be shared.

=LAMBDA(a,fn,o,k,ai,i,
    LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),
       IF(j=0,IFERROR(ai,""),
          T_AGG(a,fn,o,k,IF(s=j,
             IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values"),
               ai),j-1))
    )
)

AGG study.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	Z	AA	AB	AC
1	a1					Task 7: Wrap up all AGGREGATE related functions (AGG,AGGK, AMM) functionality in one function (T_AGG)
2	5	3	6			step 1 : Identify F(x,fn,o,k) , needs to have all arguments possible in AGGREGATE, and according to fn argument 1<=fn<=13 or 14<=fn<=19 proper AGGREGATE forms are called (with k or without k)
3	4	2	g			Also we added MODE.MULT functionality for a new fn value fn=20. All this in a simple IFS formula.
4	d	5	6			Note: if fn<>(14,19), even if k has a value , will be ignored because the formula calls the AGGREGATE without k form.
5	3	k	7			F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values")
6	a2					step 2: we create the tool recursive function using the kit template, as above (T_AGG)
7	w	2	3	5				o=6	k,ignored
8	4	3	2	-1				fn	1	2	3	4	5	6	7	8	9	10	11	12	13		20
9	0	-1	z	x					4.556	9	12	7	2	453600	1.667	1.571	41	2.778	2.469	5	5		5	3	6
10	a3								1.889	9	12	5	-1	0	2.147	2.025	17	4.611	4.099	2	2		2	3	-1
11	7	b	5	y					2.269	13	20	8	-5	-9072000	4.226	4.06	29.5	17.86	16.49	3	6		6	-1
12	w	6	-1	-1					1.615	13	20	5	-3	648000	3.15	3.027	21	9.923	9.16	3	5		5	5
13	-2	2.5	-5	8					=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),I8,6,,,)														↑
14	3	d	4	-3																	=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),W8,6,,,)
15	6	q	w	k			k	2	2	0.2	2	0.2	2								(fn 20 is in the upper section because does not need k argument)
16	a4						fn	14	15	16	17	18	19
17	t	-3	y	h	-2			6	3	3	5	3	5
18	h	4	l	5	4			4	-1	-0.4	2	-1	2
19	5	h	1	-3	3			7	-3	-1.6	3	-2.2	3
20	3	1	-2	5	k			5	-3	-2	3	-2.2	3
21								=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),H16,6,H15,,)
22
23									single cells formulas
24									=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,13),6,,,)
25									4.556	9	12	7	2	453600	1.667	1.571	41	2.778	2.469	5	5
26									1.889	9	12	5	-1	0	2.147	2.025	17	4.611	4.099	2	2
27									2.269	13	20	8	-5	-9072000	4.226	4.06	29.5	17.86	16.49	3	6
28									1.615	13	20	5	-3	648000	3.15	3.027	21	9.923	9.16	3	5
29
30								=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,6,14,1),6,{2,2,0.2,2,0.2,2},,)
31								6	3	3	5	3	5
32								4	-1	-0.4	2	-1	2
33								7	-3	-1.6	3	-2.2	3
34								5	-3	-2	3	-2.2	3
35
T_AGG

Cell Formulas
Range	Formula
W9:Y12,I9:U12	I9	=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),I8,6,,,)
I13,H21	I13	=FORMULATEXT(I9)
U14	U14	=FORMULATEXT(W9)
H17:M20	H17	=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),H16,6,H15,,)
I24,H30	I24	=FORMULATEXT(I25)
I25:U28	I25	=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,13),6,,,)
H31:M34	H31	=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,6,14,1),6,{2,2,0.2,2,0.2,2},,)
Dynamic array formulas.

 

[Xlambda]

Task 8: Filter,Unique,Sort an array "range", into an array of rows, 1 row for each array, create a !!recursive!! tool T_FUS(a,f,u,s,ai,i), based on ARF kit template. Calls AFLAT
a: single array or array "range" (a1,a2...an) ( All recursive functions created with the kit work fine for a single array or an array "range")
f: filter argument, 0, filters out only blanks/null strings,-1,filters text, 1, filters numeric values
u: unique argument, 0, does nothing, 1, extracts unique values
s: sort argument, 0, no sorting, 1, ascending order, -1,descending order
ai,i: arguments carriers of the recursive functionality, always ignored
Note: The order of operations triggered by the arguments is from left to right, or as an inside out representation SORT(UNIQUE(FILTER(..)))

=LAMBDA(a,f,u,s,ai,i,
    LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),
       IF(j=0,IFERROR(ai,""),T_FUS(a,f,u,s,IF(q=j,
           LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")),
              ai),j-1))
    )
)

AGG study.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	Z	AA	AB	AC
1	Task 8: Filter,Unique,Sort an array "range", into an array of rows, 1 row for each array, create a recursive tool T_FUS(a,f,u,s,ai,i)
2
3		step 1: identifying arguments and their values
4		f		u		s
5		-1		0		-1			a1				a2					a3					a4
6		0		1		0			5	3	6		w	2	3	5		7	b	5	y		t	-3	y	h	-2
7		1				1			4	2	g		4	3	2	-1		w	6	-1	-1		h	4	l		4
8		text		no unique		descending			d	5	6		0	-1	z	x		-2	2.5		8		5	h	1	-3	3
9		no blanks		unique		no sort			3	k	7							3	d		-3		3	1	-2	5	k
10		numeric				ascending												6	q	w	k
11
12	step 2: Identify F(x,f,u,s) for a single array
13	=LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))
14	check functionality for a3 array
15	=LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))(R6:U10,1,1,1)
16	-3	-2	-1	2.5	3	5	6	7	8
17	F(x,f,u,s) will be whatever comes after LET, we do this to save defining another sepparate lambda, the variables will be predifined beforehand by the main function in the kit structure
18	F(x,f,u,s)=LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values"))
19
20	step 3: create the tool lambda using ARF recursive function kit template
21	ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))
22	As I said before, we have to check consistency of variables and arguments when using the kit
23	We notice that the kit has "s" variable already , and the F(x) has an "s" argument (sort , so we have to change that, we change the varable "s" in the kit with "q"
24	Also we replace ai, final vector that carries the result with IFERROR(ai,""), to handle eventual NA errors
25	ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ARF(a,IF(q=j,F(x),ai),j-1))))
26	Now replacing F(x) will get to T_FUS, our function for the task:
27	T_FUS(a,f,u,s,ai,i)=LAMBDA(a,f,u,s,ai,i,LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),T_FUS(a,f,u,s,IF(q=j, LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")),ai),j-1))))
28	Note: LET inside LET is very versatile
29		f,0,u,0,s,0 (no blanks,no unique,no filter) (remove blanks,rest unchanged)
30		=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),,,,,)
31		5	3	6	4	2	g	d	5	6	3	k	7
32		w	2	3	5	4	3	2	-1	0	-1	z	x
33		7	b	5	y	w	6	-1	-1	-2	2.5	8	3	d	-3	6	q	w	k
34		t	-3	y	h	-2	h	4	l	4	5	h	1	-3	3	3	1	-2	5	k
35
36		f,1,u,1,s,1 (unique numbers ascending order)										f,-1,u,1,s,-1 (unique text descending order)
37		=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),1,1,1,,)										=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),-1,1,-1,,)
38		2	3	4	5	6	7					k	g	d
39		-1	0	2	3	4	5					z	x	w
40		-3	-2	-1	2.5	3	5	6	7	8		y	w	q	k	d	b
41		-3	-2	1	3	4	5					y	t	l	k	h
42
43		If filter has no results example
44		a1			a2				=T_FUS((B45:C46,E45:F46),1,1,1,,)							=T_FUS((B45:C46,E45:F46),-1,1,1,,)
45		a	b		1	2										a	b	c
46		c	a		3	1			1	2	3
47
48		If you want other value than null string spot "v" variable inside formula , after second LET …v,FILTER(y,z,"")…and change "" , for example with "not found"
49
50	If wrong argument value
51
52	=T_FUS((B45:C46,E45:F46),1,2,1,,)
53	check values
54	check values
55
T_FUS

Cell Formulas
Range	Formula
A15,A52,P44,I44,L37,B37,B30	A15	=FORMULATEXT(A16)
A16:I16	A16	=LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))(R6:U10,1,1,1)
B31:T34	B31	=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),,,,,)
B38:J41	B38	=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),1,1,1,,)
L38:Q41	L38	=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),-1,1,-1,,)
I45:K46	I45	=T_FUS((B45:C46,E45:F46),1,1,1,,)
P45:R46	P45	=T_FUS((B45:C46,E45:F46),-1,1,1,,)
A53:A54	A53	=T_FUS((B45:C46,E45:F46),1,2,1,,)
Dynamic array formulas.

 

[Xlambda]

A1RF !! recursive !! DIY Array 1 Recursive Function kit (Single Array Recursive Function kit)
A1RF(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE( n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1RF(a,d,IF(s=j,F(x),ai),j-1))))
a: array
d: "direction" argument, 0 or ignored, horizontal (by rows), 1, vertical (by columns)
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
Task 9: If we were able to write recursive functions for n arrays let's write a function kit for a single (one) array that has n rows/columns

AGG study.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Task 9: If we were able to write recursive functions for n arrays let's write a function kit for a single (one) array that has n rows/columns
2	Since an array can have 2 dimensions will define 2 kits A1RFH and A1RFV (array one recursive function kit horizontal/vertical) or a single function A1RF for both "directions"(extra arg. "d")
3	9.1	A1RFH
4		We will still use universal ARF function kit template structure:
5	ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))
6		we replace:
7		nr.arrays n,AREAS(a) with nr.rows n,ROWS(a)
8		x,INDEX(a,,,j) extracts from the "range" (a1,a2….aj…an) one array at a time for every iteration in the recursive cycle
9		will replace it with x,INDEX(a,j,) extracts one full row at a time for every iteration operation in the recursive cycle
10	A1RFH(a,ai,i)=LAMBDA(a,ai,i,LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,A1RFH(a,IF(s=j,F(x),ai),j-1))))
11
12	9.2	A1RFV
13		we replace:
14		nr.arrays n,AREAS(a) with nr.columns n,COLUMNS(a)
15		x,INDEX(a,,,j) extracts from the "range" (a1,a2….aj…an) one array at a time for every iteration in the recursive cycle
16		will replace it with x,INDEX(a,,j) extracts one full column at a time for every iteration operation in the recursive cycle
17		also we have to change the orientation of the building result engine
18		will replace s,SEQUENCE(n) with s,SEQUENCE(,n)
19	A1RFV(a,ai,i)=LAMBDA(a,ai,i,LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,A1RFV(a,IF(s=j,F(x),ai),j-1))))
20	Note: As a general note, to change the direction or orientation of an array outcome we can also use double transpose.
21		A1RFV(a)=TRANSPOSE(A1RFH(TRANSPOSE(a))
22
23	9.3	A1RF
24		We noticed so far that second part of the functions (the building result engine, the ai argument) always stays the same, regardless the orientation
25		IF(s=j,F(x),ai). This means, we can introduce a separate argument for orientation/direction , d, with 2 values to cover both scenarios
26		d: 0 or ignored for horizontal (by rows), 1, for vertical (by columns)
27	A1RF(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE(n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1RF(a,d,IF(s=j,F(x),ai),j-1))))
28
29		Let's put this to test for a simple operation, SUM, function name A1SUM using the kit template so, F(x)=SUM(x)
30	A1SUM(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE(n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1SUM(a,d,IF(s=j,SUM(x),ai),j-1))))
31
32		sample					d=0			check
33		=SEQUENCE(6,4)					=A1SUM(B34#,,,)			=SUM(B34:E34)
34		1	2	3	4		10			10
35		5	6	7	8		26			26
36		9	10	11	12		42			42
37		13	14	15	16		58			58
38		17	18	19	20		74			74
39		21	22	23	24		90			90
40
41		d=1
42		=A1SUM(B34#,1,,)
43		66	72	78	84
44		check
45		=SUM(B34:B39)
46		66	72	78	84
47
A1RF post

Cell Formulas
Range	Formula
B33,B45,B42,G33,J33	B33	=FORMULATEXT(B34)
B34:E39	B34	=SEQUENCE(6,4)
G34:G39	G34	=A1SUM(B34#,,,)
J34:J39	J34	=SUM(B34:E34)
B43:E43	B43	=A1SUM(B34#,1,,)
B46:E46	B46	=SUM(B34:B39)
Dynamic array formulas.