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]

I have never used for any of my recursive functions a trick I've learned from Mynda long time ago.
Excel LAMBDA Recursion + a Trick for Evaluating in a Cell
Using this trick, we do not need to define a recursive lambda, it calls itself in the cell.
Here are some examples, basic simple recursive lambdas, to visualize how it works, various scenarios.

Excel-Challenge-License-Plates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Basic recursive lambda that reverses any string. (1 variable, "a")
3
4		defined REV(a)=LAMBDA(a,LET(n,LEN(a),x,RIGHT(a,1),IF(n=0,a,x&REV(LEFT(a,n-1)))))
5							check
6				=REV(B7)			=TEXTJOIN(,,MID(B7,SEQUENCE(,LEN(B7),LEN(B7),-1),1))
7		aBcD,:123		321:,DcBa			321:,DcBa
8
9		to make it call itself in a cell, not defined
10
11				=LET(a,B7,fn,LAMBDA(y,a,LET(n,LEN(a),x,RIGHT(a,1),IF(n=0,a,x&y(y,LEFT(a,n-1))))),fn(fn,a))
12				321:,DcBa
13
14
15		Recursive lambda that adds all digits. (2 variables, "a" and "s")
16
17		defined ADDIG(a,[s ])=LAMBDA(a,[s ],LET(n,LEN(a),x,s+RIGHT(a,1),IF(n=0,s,ADDIG(LEFT(a,n-1),x))))
18							check
19				=ADDIG(B20)			=SUM(--MID(B20,SEQUENCE(LEN(B20)),1))
20	text→	32851209		30			30
21
22				=ADDIG(B23)
23	number→	32851209		30
24
25		in cell, not defined		=LET(a,B20,fn,LAMBDA(y,a,[s ],LET(n,LEN(a),x,s+RIGHT(a,1),IF(n=0,s,y(y,LEFT(a,n-1),x)))),fn(fn,a))
26				30
27
in cell recursive 1

Cell Formulas
Range	Formula
G6,D22,G19,D19,D11,D6	G6	=FORMULATEXT(G7)
D7	D7	=REV(B7)
G7	G7	=TEXTJOIN(,,MID(B7,SEQUENCE(,LEN(B7),LEN(B7),-1),1))
D12	D12	=LET(a,B7,fn,LAMBDA(y,a,LET(n,LEN(a),x,RIGHT(a,1),IF(n=0,a,x&y(y,LEFT(a,n-1))))),fn(fn,a))
D20,D23	D20	=ADDIG(B20)
G20	G20	=SUM(--MID(B20,SEQUENCE(LEN(B20)),1))
D26	D26	=LET(a,B20,fn,LAMBDA(y,a,[s],LET(n,LEN(a),x,s+RIGHT(a,1),IF(n=0,s,y(y,LEFT(a,n-1),x)))),fn(fn,a))

 

[Xlambda]

Excel-Challenge-License-Plates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		Recursive lambda that extracts only the digits. (2 variables, double self calling)
3
4		defined EXDIG(a,[d])=LAMBDA(a,[d],LET(r,IF(d="","",d),n,LEN(a),x,RIGHT(a,1),in,ISNUMBER(--x),IF(n=0,d,IF(in,EXDIG(LEFT(a,n-1),x&r),EXDIG(LEFT(a,n-1),r)))))
5
6				=EXDIG(B7)
7		1g23T(45)Pz%6		123456
8
9		in cell, not defined		=LET(a,B7,fn,LAMBDA(y,a,[d],LET(r,IF(d="","",d),n,LEN(a),x,RIGHT(a,1),in,ISNUMBER(--x),IF(n=0,d,IF(in,y(y,LEFT(a,n-1),x&r),y(y,LEFT(a,n-1),r))))),fn(fn,a))
10				123456
11
12
13		Recursive lambda that extracts and adds digits
14
15		defined ADDEXDIG(a,[s ])=LAMBDA(a,[s ],LET(n,LEN(a),x,s+IFERROR(--RIGHT(a,1),0),IF(n=0,s,ADDEXDIG(LEFT(a,n-1),x))))
16							checked with nested recursive lambdas
17				=ADDEXDIG(B18)			=ADDIG(EXDIG(B7))
18		1g23T(45)Pz%6		21			21
19
20		in cell, not defined		=LET(a,B18,fn,LAMBDA(y,a,[s ],LET(n,LEN(a),x,s+IFERROR(--RIGHT(a,1),0),IF(n=0,s,y(y,LEFT(a,n-1),x)))),fn(fn,a))
21				21
22
23
24		As we see in this various scenarios, the algorithm is straight forward. If a defined recursive lambda is called RL(a,b), we construct a 3 parts LET
25
26		RL(a,b)=LAMBDA(a,b,LET(…..a1….a2…IF(exit cond.,acumulator,RL(a1,b1))))
27
28		=LET(a,cell,fn,LAMBDA(y,a,b,LET(….a1…a2….IF(exit cond.,acumulator,y(y,a1,b1)))),fn(fn,a,b))
29
30		- 1st part input variable, 2nd part lambda variable, 3rd part calling lambda variable
31
in cell recursive 2

Cell Formulas
Range	Formula
D6,G17,D17,D9	D6	=FORMULATEXT(D7)
D7	D7	=EXDIG(B7)
D10	D10	=LET(a,B7,fn,LAMBDA(y,a,[d],LET(r,IF(d="","",d),n,LEN(a),x,RIGHT(a,1),in,ISNUMBER(--x),IF(n=0,d,IF(in,y(y,LEFT(a,n-1),x&r),y(y,LEFT(a,n-1),r))))),fn(fn,a))
D18	D18	=ADDEXDIG(B18)
G18	G18	=ADDIG(EXDIG(B7))
D21	D21	=LET(a,B18,fn,LAMBDA(y,a,[s],LET(n,LEN(a),x,s+IFERROR(--RIGHT(a,1),0),IF(n=0,s,y(y,LEFT(a,n-1),x)))),fn(fn,a))

 

[Xlambda]

All recursive functions:

REV(a) Reverse any string
a: single value/cell reference, any string

=LAMBDA(a, LET(n, LEN(a), x, RIGHT(a, 1), IF(n = 0, a, x & REV(LEFT(a, n - 1)))))

ADDIG(a,[sm]) Adds all digits of a number or number as text
a: single value/cell reference, number, or numeric text value (only digits)
[sm]: always omitted, (initial value always 0) after 1st iteration is used as an "acumulator" that stores the sum of digits through iterations.

=LAMBDA(a, [sm],
    LET(n, LEN(a), x, sm + RIGHT(a, 1), IF(n = 0, sm, ADDIG(LEFT(a, n - 1), x)))
)

EXDIG(a,[d]) Extracts all the digits in a string
a: single value/cell reference, any string
[d]: always omitted (initial value empty string ""), after 1st iteration is used as an "acumulator" that appends only the digits, ignoring text

=LAMBDA(a, [d],
    LET(
        r, IF(d = "", "", d),
        n, LEN(a),
        x, RIGHT(a, 1),
        in, ISNUMBER(--x),
        IF(n = 0, d, IF(in, EXDIG(LEFT(a, n - 1), x & r), EXDIG(LEFT(a, n - 1), r)))
    )
)

ADDEXDIG(a,[sm]) Add Extracted Digits (have used sm instead of previous s because [ s ] triggers strikethrough format, straight line cutting through the center)
a: single value/cell reference, any string
[sm]: always omitted, (initial value always 0) after 1st iteration is used as an "acumulator" that stores the sum of digits through iterations, ignoring text

=LAMBDA(a, [sm],
    LET(
        n, LEN(a),
        x, sm + IFERROR(--RIGHT(a, 1), 0),
        IF(n = 0, sm, ADDEXDIG(LEFT(a, n - 1), x))
    )
)

Excel-Challenge-License-Plates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2				extracting digits			adding digits				extracting and adding digits
3				=MAP(B4:B6,LAMBDA(x,EXDIG(x)))			=MAP(D4#,LAMBDA(x,ADDIG(x)))				=MAP(B4:B6,LAMBDA(x,ADDEXDIG(x)))
4		e7R%-;6gH95>28j		769528			37				37
5		90876xRTy2@#57		90876257			44				44
6		b9999K9888888x777		99999888888777			114				114
7
8		reversed
9		=MAP(B4:B6,LAMBDA(x,REV(x)))		=MAP(B10:B12,LAMBDA(x,EXDIG(x)))			=MAP(D10#,LAMBDA(x,ADDIG(x)))				=MAP(B10:B12,LAMBDA(x,ADDEXDIG(x)))
10		j82>59Hg6;-%R7e		825967			37				37
11		75#@2yTRx67809		75267809			44				44
12		777x8888889K9999b		77788888899999			114				114
13
14							sum of digits in reversed order deliver same results
15
16			Note:
17			EXDIG keeps the result in text format for not allowing Excel to turn it into scientific format with E when many digits are extracted.
18
19				=--D6
20				9.99999E+13
21
in cell recursive 3

Cell Formulas
Range	Formula
D3,G3,K3,D19,B9,D9,G9,K9	D3	=FORMULATEXT(D4)
D4:D6,D10:D12	D4	=MAP(B4:B6,LAMBDA(x,EXDIG(x)))
G4:G6,G10:G12	G4	=MAP(D4#,LAMBDA(x,ADDIG(x)))
K4:K6,K10:K12	K4	=MAP(B4:B6,LAMBDA(x,ADDEXDIG(x)))
B10:B12	B10	=MAP(B4:B6,LAMBDA(x,REV(x)))
D20	D20	=--D6
Dynamic array formulas.

 

[Xlambda]

4th solution for even/odd challenge, in cell recursive lambda, not defined, and 5th, a formula that calls EXDIG.
In total, 5 solutions as alternatives to using MID

Excel-Challenge-License-Plates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	License Plate Challenge
2		4th solution, in cell recursive lambda
3		=LET(a,A6,fn,LAMBDA(y,a,LET(c,1&a,n,LEN(c),x,ISODD(RIGHT(c,1)),IF(ISERR(x),y(y,LEFT(c,n-1)),IF(x,"Odd","Even")))),fn(fn,a))
4		↓↓
5	Plate Number	Odd/Even		5th →	=LET(a,A6:A55,m,MAP(a,LAMBDA(x,EXDIG(x))),IF(IFERROR(ISODD(m),1),"Odd","Even"))
6	123M57	Odd			Odd
7	1COU886	Even			Even		check
8	1D48821	Odd			Odd		=AND(tblChallenge[Odd/Even]=E6#)
9	1DVT189	Odd			Odd		TRUE
10	21W7454	Even			Even
11	27U17	Odd			Odd
12	2IGR878	Even			Even
13	2RDZ434	Even			Even
14	2SWQ185	Odd			Odd
15	3FII697	Odd			Odd
16	3QOW866	Even			Even
17	3RQC229	Odd			Odd
18	3RSZ114	Even			Even
19	4FXL296	Even			Even
20	4KUC981	Odd			Odd
21	4QKB212	Even			Even
22	4QRG241	Odd			Odd
23	4RVF139	Odd			Odd
24	5ACY568	Even			Even
25	5IYF234	Even			Even
26	5KFC128	Even			Even
27	5NFD419	Odd			Odd
28	5RXA984	Even			Even
29	5TGB871	Odd			Odd
30	5UEZ777	Odd			Odd
31	5WKT763	Odd			Odd
32	6LJT722	Even			Even
33	6MVB117	Odd			Odd
34	6XZR265	Odd			Odd
35	71F27UJ	Odd			Odd
36	745Y4I	Even			Even
37	76736Q	Even			Even
38	7FOB933	Odd			Odd
39	7JCM371	Odd			Odd
40	884PCX	Even			Even
41	8JHC777	Odd			Odd
42	8RDQ178	Even			Even
43	8TMW513	Odd			Odd
44	9PMH329	Odd			Odd
45	9UGW414	Even			Even
46	9UUL349	Odd			Odd
47	BEACH	Odd			Odd
48	G97C8T	Even			Even
49	H1R226I	Even			Even
50	KL7469C	Odd			Odd
51	RUSS	Odd			Odd
52	S94CO	Even			Even
53	T5A5MEN	Odd			Odd
54	YELOSTN	Odd			Odd
55	YOSEMIT	Odd			Odd
56
Challenge

Cell Formulas
Range	Formula
B3	B3	=FORMULATEXT(B6)
E5,G8	E5	=FORMULATEXT(E6)
E6:E55	E6	=LET(a,A6:A55,m,MAP(a,LAMBDA(x,EXDIG(x))),IF(IFERROR(ISODD(m),1),"Odd","Even"))
G9	G9	=AND(tblChallenge[Odd/Even]=E6#)
B6:B55	B6	=LET(a,A6,fn,LAMBDA(y,a,LET(c,1&a,n,LEN(c),x,ISODD(RIGHT(c,1)),IF(ISERR(x),y(y,LEFT(c,n-1)),IF(x,"Odd","Even")))),fn(fn,a))
Dynamic array formulas.

 

[Xlambda]

Sunday's shorts.
Recursive functions can deal with multiple arguments, ("accumulators" or counters)
Simple basic example to show the technique of replacing recursive functionality using REDUCE with 2 "accumulator" arguments (array of accumulators) and an exit condition.
Task: Find the index number of a vector and the closest running total value over a given amount.
RTL(a,[rt],[i ]) Running Total Limit, recursive!!
a: column vector
[rt]: "accumulator" of running total
[i ]: index count

=LAMBDA(a,[rt],[i ],LET(j,IF(i,i,1),x,rt+INDEX(a,j),IF(x>=$C$3,HSTACK(j,x),RTL(a,x,j+1))))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2			limit					2. reduce, array of accumulators
3			2000					=REDUCE({0,0},C8:C37,LAMBDA(v,i,LET(x,INDEX(v,2),HSTACK(INDEX(v,1)+IF(x<C3,1,0),x+IF(x<C3,i,0)))))
4								↓↓↓			3. classic
5								↓↓↓			=LET(a,C8:C37,l,C3,s,SCAN(0,a,LAMBDA(v,i,(v+i))),x,XMATCH(l,s,1),HSTACK(x,INDEX(s,x)))
6					1. recursive			↓↓↓			↓↓↓					checking
7					=RTL(C8:C38)			↓↓↓			↓↓↓					=SCAN(0,C8:C37,LAMBDA(v,i,v+i))
8		1	98		20	2107		20	2107		20	2107			1	98
9		2	155												2	253
10		3	75												3	328
11		4	178												4	506
12		5	31												5	537
13		6	2												6	539
14		7	196												7	735
15		8	56												8	791
16		9	128												9	919
17		10	104												10	1023
18		11	60												11	1083
19		12	182												12	1265
20		13	11												13	1276
21		14	182												14	1458
22		15	94												15	1552
23		16	153												16	1705
24		17	94												17	1799
25		18	80												18	1879
26		19	96												19	1975
27		20	132												20	2107
28		21	200												21	2307
29		22	155												22	2462
30		23	48												23	2510
31		24	54												24	2564
32		25	140												25	2704
33		26	9												26	2713
34		27	117												27	2830
35		28	97												28	2927
36		29	168												29	3095
37		30	28												30	3123
38
Sheet1

Cell Formulas
Range	Formula
H3	H3	=FORMULATEXT(H8)
K5	K5	=FORMULATEXT(K8)
E7,P7	E7	=FORMULATEXT(E8)
B8:B37,O8:O37	B8	=SEQUENCE(30)
E8:F8	E8	=RTL(C8:C38)
H8:I8	H8	=REDUCE({0,0},C8:C37,LAMBDA(v,i,LET(x,INDEX(v,2),HSTACK(INDEX(v,1)+IF(x<C3,1,0),x+IF(x<C3,i,0)))))
K8:L8	K8	=LET(a,C8:C37,l,C3,s,SCAN(0,a,LAMBDA(v,i,(v+i))),x,XMATCH(l,s,1),HSTACK(x,INDEX(s,x)))
P8:P37	P8	=SCAN(0,C8:C37,LAMBDA(v,i,v+i))
Dynamic array formulas.

 

[Xlambda]

Structural differences btwn solutions.
1. recursive: when limit condition is met, function exits iterations, efficient but limited nr. of recursive iterations, not good for large vectors
2. reduce: when limit condition is met, array of accumulators stop accumulating, iterations will have to continue, no iterations limit other than excel real estate limits.
3. classic: even if this example has no recursive approach solution, the technique used by reduce with an array of accumulators simulating an exit condition could be useful for more complex scenarios.

 

[Xlambda]

Sunday fun with lambda "short" formulas, inspired from YT shorts

From Wikipedia:
6174 is known as Kaprekar's constant after the Indian mathematician D. R. Kaprekar. This number is renowned for the following rule:
Take any four-digit number, using at least two different digits (leading zeros are allowed).
Arrange the digits in descending and then in ascending order to get two four-digit numbers, adding leading zeros if necessary.
Subtract the smaller number from the bigger number.
Go back to step 2 and repeat.
The above process, known as Kaprekar's routine, will always reach its fixed point, 6174, in at most 7 iterations. Once 6174 is reached, the process will continue yielding 7641 – 1467 = 6174. For example, choose 1495:
9541 – 1459 = 8082
8820 – 0288 = 8532
8532 – 2358 = 6174
7641 – 1467 = 6174

Recursive function to check it (1st draft):
KPRKR(n )

=LAMBDA(n,LET(a,MID(n,SEQUENCE(LEN(n)),1),x,CONCAT(SORT(a,,-1)),y,CONCAT(SORT(a)),z,x-y,IF(z=n,n,KPRKR(z))))

Kaprekar.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
2		=KPRKR(1495)			start			CF		values <> 6174
3		6174			1000
4
5		=KPRKR(1482)			=SEQUENCE(10,20,1000)
6		6174			1000	1001	1002	1003	1004	1005	1006	1007	1008	1009	1010	1011	1012	1013	1014	1015	1016	1017	1018	1019
7					1020	1021	1022	1023	1024	1025	1026	1027	1028	1029	1030	1031	1032	1033	1034	1035	1036	1037	1038	1039
8		=KPRKR(8273)			1040	1041	1042	1043	1044	1045	1046	1047	1048	1049	1050	1051	1052	1053	1054	1055	1056	1057	1058	1059
9		6174			1060	1061	1062	1063	1064	1065	1066	1067	1068	1069	1070	1071	1072	1073	1074	1075	1076	1077	1078	1079
10					1080	1081	1082	1083	1084	1085	1086	1087	1088	1089	1090	1091	1092	1093	1094	1095	1096	1097	1098	1099
11					1100	1101	1102	1103	1104	1105	1106	1107	1108	1109	1110	1111	1112	1113	1114	1115	1116	1117	1118	1119
12					1120	1121	1122	1123	1124	1125	1126	1127	1128	1129	1130	1131	1132	1133	1134	1135	1136	1137	1138	1139
13					1140	1141	1142	1143	1144	1145	1146	1147	1148	1149	1150	1151	1152	1153	1154	1155	1156	1157	1158	1159
14					1160	1161	1162	1163	1164	1165	1166	1167	1168	1169	1170	1171	1172	1173	1174	1175	1176	1177	1178	1179
15					1180	1181	1182	1183	1184	1185	1186	1187	1188	1189	1190	1191	1192	1193	1194	1195	1196	1197	1198	1199
16
17					=MAP(E6#,KPRKR)
18					0	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	0	6174	6174	6174	6174	6174	6174	6174	6174
19					6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
20					6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
21					6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
22					6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
23					6174	0	6174	6174	6174	6174	6174	6174	6174	6174	0	0	0	6174	6174	6174	6174	6174	6174	6174
24					6174	0	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
25					6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
26					6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
27					6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
28
29
K 1

Cell Formulas
Range	Formula
B2,E17,B8,E5,B5	B2	=FORMULATEXT(B3)
B3	B3	=KPRKR(1495)
B6	B6	=KPRKR(1482)
E6:X15	E6	=SEQUENCE(10,20,1000)
B9	B9	=KPRKR(8273)
E18:X27	E18	=MAP(E6#,KPRKR)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E18:X27	Expression	=E18=0	text	NO
E6:X15	Expression	=E18=0	text	NO

 

[Xlambda]

To deal with the "0"'s there is an algorithm rule:
The only four-digit numbers for which Kaprekar's routine does not reach 6174 are repdigits such as 1111, which give the result 0000 after a single iteration. All other four-digit numbers eventually reach 6174 if leading zeros are used to keep the number of digits at 4. For numbers with three identical numbers and a fourth number that is one number higher or lower (such as 2111), it is essential to treat 3-digit numbers with a leading zero; for example: 2111 – 1112 = 0999; 9990 – 999 = 8991; 9981 – 1899 = 8082; 8820 – 288 = 8532; 8532 – 2358 = 6174.
Therefore, here is draft nr. 2 of recursive function:
KPK(n )

=LAMBDA(n,LET(a,MID(n,SEQUENCE(LEN(n)),1),x,CONCAT(SORT(a,,-1)),y,CONCAT(SORT(a)),z,x-y,v,IF(LEN(z)<LEN(n),z*10,z),IF(v=n,n,KPK(v))))

Kaprekar.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
2		start			CF		values <> 6174
3		1000
4
5		=SEQUENCE(10,20,B3)
6		1000	1001	1002	1003	1004	1005	1006	1007	1008	1009	1010	1011	1012	1013	1014	1015	1016	1017	1018	1019
7		1020	1021	1022	1023	1024	1025	1026	1027	1028	1029	1030	1031	1032	1033	1034	1035	1036	1037	1038	1039
8		1040	1041	1042	1043	1044	1045	1046	1047	1048	1049	1050	1051	1052	1053	1054	1055	1056	1057	1058	1059
9		1060	1061	1062	1063	1064	1065	1066	1067	1068	1069	1070	1071	1072	1073	1074	1075	1076	1077	1078	1079
10		1080	1081	1082	1083	1084	1085	1086	1087	1088	1089	1090	1091	1092	1093	1094	1095	1096	1097	1098	1099
11		1100	1101	1102	1103	1104	1105	1106	1107	1108	1109	1110	1111	1112	1113	1114	1115	1116	1117	1118	1119
12		1120	1121	1122	1123	1124	1125	1126	1127	1128	1129	1130	1131	1132	1133	1134	1135	1136	1137	1138	1139
13		1140	1141	1142	1143	1144	1145	1146	1147	1148	1149	1150	1151	1152	1153	1154	1155	1156	1157	1158	1159
14		1160	1161	1162	1163	1164	1165	1166	1167	1168	1169	1170	1171	1172	1173	1174	1175	1176	1177	1178	1179
15		1180	1181	1182	1183	1184	1185	1186	1187	1188	1189	1190	1191	1192	1193	1194	1195	1196	1197	1198	1199
16
17		IMP Note: KPK is a single argument function => as lambda helper function in MAP, instead of LAMBDA(x,KPX(x)) we can write simplified:
18		=MAP(B6#,KPK)
19		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
20		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
21		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
22		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
23		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
24		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	0	6174	6174	6174	6174	6174	6174	6174	6174
25		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
26		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
27		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
28		6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174	6174
29
K 2

Cell Formulas
Range	Formula
B5,B18	B5	=FORMULATEXT(B6)
B6:U15	B6	=SEQUENCE(10,20,B3)
B19:U28	B19	=MAP(B6#,KPK)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B19:U28	Expression	=B19=0	text	NO
B6:U15	Expression	=B19=0	text	NO

 

[Xlambda]

Side off topic Note: I know that these formulas have no real-life use, but at least, I hope they are fun, and the techniques used are useful enough to be shared. Lambda fitness training for the real world.
Now, let's check the new function for all 9000 numbers between 1000 and 9999 to see if delivers only 6174 (except the nr. with repeated digits like 1111,2222,...9999)

Kaprekar.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Checking all values btwn 1000 and 9999 (all 9000 values)
2						all values = 0		all values = 6174
3		=SEQUENCE(9000,,1000)		=MAP(B4#,LAMBDA(x,KPK(x)))		=FILTER(B4#,D4#=0)		=FILTER(B4#,D4#=6174)
4		1000		6174		1111		1000
5		1001		6174		2222		1001		=ROWS(F4#)+ROWS(H4#)
6		1002		6174		3333		1002		9000
7		1003		6174		4444		1003		=>
8		1004		6174		5555		1004		all values are 6174, except the expected 9 0's values
9		1005		6174		6666		1005
10		1006		6174		7777		1006
11		1007		6174		8888		1007
12		1008		6174		9999		1008
13		1009		6174				1009
14		1010		6174				1010
15		1011		6174				1011
16		1012		6174				1012
17		1013		6174				1013
18		1014		6174				1014
19		1015		6174				1015
20		1016		6174				1016
21		1017		6174				1017
22		1018		6174				1018
23		1019		6174				1019
24		1020		6174				1020
25		1021		6174				1021
26		1022		6174				1022
27		1023		6174				1023
28		1024		6174				1024
29		1025		6174				1025
30		1026		6174				1026
31		1027		6174				1027
32		1028		6174				1028
33		1029		6174				1029
34		1030		6174				1030
35		1031		6174				1031
36		1032		6174				1032
37		1033		6174				1033
38		1034		6174				1034
39		1035		6174				1035
40		1036		6174				1036
41		1037		6174				1037
42		1038		6174				1038
43		1039		6174				1039
44		1040		6174				1040
45		1041		6174				1041
46		1042		6174				1042
47		1043		6174				1043
48		1044		6174				1044
49		1045		6174				1045
50		1046		6174				1046
51		1047		6174				1047
52		1048		6174				1048
53		1049		6174				1049
54		1050		6174				1050
55		1051		6174				1051
56		1052		6174				1052
57		1053		6174				1053
58		1054		6174				1054
59		1055		6174				1055
60		1056		6174				1056
61		1057		6174				1057
62		1058		6174				1058
63		1059		6174				1059
64		1060		6174				1060
65		1061		6174				1061
K 3

Cell Formulas
Range	Formula
B3,D3,F3,H3,J5	B3	=FORMULATEXT(B4)
B4:B9003	B4	=SEQUENCE(9000,,1000)
D4:D9003	D4	=MAP(B4#,LAMBDA(x,KPK(x)))
F4:F12	F4	=FILTER(B4#,D4#=0)
H4:H8994	H4	=FILTER(B4#,D4#=6174)
J6	J6	=ROWS(F4#)+ROWS(H4#)
Dynamic array formulas.

 

[Xlambda]

Mike (ExcelIsFun) asked my opinion about solving a super cool problem using recursion, The Pascal's triangle.

Here are my solutions.
Concept: Tool lambda. Calculating a line from previous line, Pascal's triangle Line
PL(x) where x: previous line (row vector)

=LAMBDA(x,HSTACK(1,DROP(x,,-1)+DROP(x,,1),1))

Recursive 1: Recursive Pascal's triangle n'th row, calls PL
RPSC(n ) n: N>=3

=LAMBDA(n,[p],LET(x,IF(ISOMITTED(p),{1,1},p),IF(COLUMNS(x)=n,p,RPSC(n,PL(x)))))

Book1
	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				Concept: Tool function PL(x) Pascal's triangle Line
2
3		n		Pascal's triangle (left aligned)
4		1		1
5		2		1	1
6		3		1	2	1
7		4		1	3	3	1
8		5		1	4	6	4	1
9		6		1	5	10	10	5	1
10		7		1	6	15	20	15	6	1
11		8		1	7	21	35	35	21	7	1
12		9		1	8	28	56	70	56	28	8	1
13		10		1	9	36	84	126	126	84	36	9	1
14		11		1	10	45	120	210	252	210	120	45	10	1
15		12		1	11	55	165	330	462	462	330	165	55	11	1
16		13		1	12	66	220	495	792	924	792	495	220	66	12	1
17		14		1	13	78	286	715	1287	1716	1716	1287	715	286	78	13	1
18		15		1	14	91	364	1001	2002	3003	3432	3003	2002	1001	364	91	14	1
19		16		1	15	105	455	1365	3003	5005	6435	6435	5005	3003	1365	455	105	15	1
20		17		1	16	120	560	1820	4368	8008	11440	12870	11440	8008	4368	1820	560	120	16	1
21		18		1	17	136	680	2380	6188	12376	19448	24310	24310	19448	12376	6188	2380	680	136	17	1
22		19		1	18	153	816	3060	8568	18564	31824	43758	48620	43758	31824	18564	8568	3060	816	153	18	1
23		20		1	19	171	969	3876	11628	27132	50388	75582	92378	92378	75582	50388	27132	11628	3876	969	171	19	1
24
25			Concept: How to calculate line 8 if we have previous line, line 7
26														=DROP(D27:J27,,-1)
27		line7		1	6	15	20	15	6	1			step1	1	6	15	20	15	6
28														=DROP(D27:J27,,1)
29													step2	6	15	20	15	6	1
30														=N27#+N29#
31													step3	7	21	35	35	21	7
32														=HSTACK(1,N31#,1)
33		line8		1	7	21	35	35	21	7	1		step4	1	7	21	35	35	21	7	1
34
35				=PL(D27:J27)
36				1	7	21	35	35	21	7	1
37
38				=PL(D33:K33)
39				1	8	28	56	70	56	28	8	1
40
Sheet1

Cell Formulas
Range	Formula
N26,D38,D35,N32,N30,N28	N26	=FORMULATEXT(N27)
N27:S27	N27	=DROP(D27:J27,,-1)
N29:S29	N29	=DROP(D27:J27,,1)
N31:S31	N31	=N27#+N29#
N33:U33	N33	=HSTACK(1,N31#,1)
D36:K36	D36	=PL(D27:J27)
D39:L39	D39	=PL(D33:K33)
Dynamic array formulas.

Book1
	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
1
2
3		=RPSC(3)
4		1	2	1
5
6		=RPSC(5)
7		1	4	6	4	1
8
9		=RPSC(10)
10		1	9	36	84	126	126	84	36	9	1
11
12		=RPSC(20)
13		1	19	171	969	3876	11628	27132	50388	75582	92378	92378	75582	50388	27132	11628	3876	969	171	19	1
14
15		=RPSC(25)
16		1	24	276	2024	10626	42504	134596	346104	735471	1307504	1961256	2496144	2704156	2496144	1961256	1307504	735471	346104	134596	42504	10626	2024	276	24	1
17
Sheet2

Cell Formulas
Range	Formula
B3,B15,B12,B9,B6	B3	=FORMULATEXT(B4)
B4:D4	B4	=RPSC(3)
B7:F7	B7	=RPSC(5)
B10:K10	B10	=RPSC(10)
B13:U13	B13	=RPSC(20)
B16:Z16	B16	=RPSC(25)
Dynamic array formulas.

Recursive 2: Recursive Pascal's entire Triangle calls PL
RPSCT(n ) n: N>=3

=LAMBDA(n,[p],LET(x,IF(ISOMITTED(p),{1,"";1,1},p),y,TAKE(x,-1),c,COLUMNS(y),IF(c=n,IFNA(p,""),RPSCT(n,VSTACK(x,PL(y))))))

Book1
	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	AE	AF
1
2		=RPSCT(3)
3		1
4		1	1
5		1	2	1
6
7		=RPSCT(30)
8		1
9		1	1
10		1	2	1
11		1	3	3	1
12		1	4	6	4	1
13		1	5	10	10	5	1
14		1	6	15	20	15	6	1
15		1	7	21	35	35	21	7	1
16		1	8	28	56	70	56	28	8	1
17		1	9	36	84	126	126	84	36	9	1
18		1	10	45	120	210	252	210	120	45	10	1
19		1	11	55	165	330	462	462	330	165	55	11	1
20		1	12	66	220	495	792	924	792	495	220	66	12	1
21		1	13	78	286	715	1287	1716	1716	1287	715	286	78	13	1
22		1	14	91	364	1001	2002	3003	3432	3003	2002	1001	364	91	14	1
23		1	15	105	455	1365	3003	5005	6435	6435	5005	3003	1365	455	105	15	1
24		1	16	120	560	1820	4368	8008	11440	12870	11440	8008	4368	1820	560	120	16	1
25		1	17	136	680	2380	6188	12376	19448	24310	24310	19448	12376	6188	2380	680	136	17	1
26		1	18	153	816	3060	8568	18564	31824	43758	48620	43758	31824	18564	8568	3060	816	153	18	1
27		1	19	171	969	3876	11628	27132	50388	75582	92378	92378	75582	50388	27132	11628	3876	969	171	19	1
28		1	20	190	1140	4845	15504	38760	77520	125970	167960	184756	167960	125970	77520	38760	15504	4845	1140	190	20	1
29		1	21	210	1330	5985	20349	54264	116280	203490	293930	352716	352716	293930	203490	116280	54264	20349	5985	1330	210	21	1
30		1	22	231	1540	7315	26334	74613	170544	319770	497420	646646	705432	646646	497420	319770	170544	74613	26334	7315	1540	231	22	1
31		1	23	253	1771	8855	33649	100947	245157	490314	817190	1144066	1352078	1352078	1144066	817190	490314	245157	100947	33649	8855	1771	253	23	1
32		1	24	276	2024	10626	42504	134596	346104	735471	1307504	1961256	2496144	2704156	2496144	1961256	1307504	735471	346104	134596	42504	10626	2024	276	24	1
33		1	25	300	2300	12650	53130	177100	480700	1081575	2042975	3268760	4457400	5200300	5200300	4457400	3268760	2042975	1081575	480700	177100	53130	12650	2300	300	25	1
34		1	26	325	2600	14950	65780	230230	657800	1562275	3124550	5311735	7726160	9657700	10400600	9657700	7726160	5311735	3124550	1562275	657800	230230	65780	14950	2600	325	26	1
35		1	27	351	2925	17550	80730	296010	888030	2220075	4686825	8436285	13037895	17383860	20058300	20058300	17383860	13037895	8436285	4686825	2220075	888030	296010	80730	17550	2925	351	27	1
36		1	28	378	3276	20475	98280	376740	1184040	3108105	6906900	13123110	21474180	30421755	37442160	40116600	37442160	30421755	21474180	13123110	6906900	3108105	1184040	376740	98280	20475	3276	378	28	1
37		1	29	406	3654	23751	118755	475020	1560780	4292145	10015005	20030010	34597290	51895935	67863915	77558760	77558760	67863915	51895935	34597290	20030010	10015005	4292145	1560780	475020	118755	23751	3654	406	29	1
38
Sheet3

Cell Formulas
Range	Formula
B2,B7	B2	=FORMULATEXT(B3)
B3:D5	B3	=RPSCT(3)
B8:AE37	B8	=RPSCT(30)
Dynamic array formulas.