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.

 

[mgirvin]

No recursion limit anymore! 10 M iterations… and no error!! Talk about nice accidental discovery : )

More versatility with recursion than iteration: the future of Microsoft programming will go through Excel Lambda who will build the new L List set of functions that blow away the ease and power of even Show Values As: Percent of Parent Row Total.

Xlambda : ) : ) : ) : ) : ) : ) : ) : ) : )

 

[Xlambda]

Check this cool video from Excel Off the Grid, titled: STOP using nested IF statements! Use these functions instead.

Except…big except …IFS and SWITCH can not include exit conditions for recursive functions. Will deliver a #NUM error => keeps recalculating all conditions
Found this the hard way, debugging LIST function that did not work properly , and had no idea that the culprit was an exit condition inside an IFS calculation.
LIST is a composed recursive function on several levels, calls itself and calls also a function that calls LIST back.
Define this test functions:
All these functions count from i=0 and as long i<n, function calls itself incrementing i by 1, and should exit when i=n, returning i value, respective n

RSW recursive switch:

=LAMBDA(n,[i],SWITCH(TRUE,i<n,RSW(n,i+1),i))

RIFS recursive ifs

=LAMBDA(n,[i],IFS(i<n,RIFS(n,i+1),1,i))

RIF recursive if regular IF function, the only one that "exits"

=LAMBDA(n,[i],IF(i<n,RIF(n,i+1),i))

and another switch one that should explicitly exit when i=3
RSWb

=LAMBDA(n,[i],SWITCH(i,3,3,RSWb(n,i+1)))

Book1
	A	B	C	D	E
1
2		=RSW(5)		=RIF(5)
3		#NUM!		5
4
5		=RIFS(5)		=RIF(10000000)
6		#NUM!		10000000
7
8		=RSWb(5)		no probl for 10M
9		#NUM!
10
Sheet1

Cell Formulas
Range	Formula
B2,B8,B5,D5,D2	B2	=FORMULATEXT(B3)
B3	B3	=RSW(5)
D3	D3	=RIF(5)
B6	B6	=RIFS(5)
D6	D6	=RIF(10000000)
B9	B9	=RSWb(5)

Lambda Functions
Name	Formula
RIF	=LAMBDA(n,,IF(i<n,RIF(n,i+1),i))

[XR][XD]RIFS[/XD][XD]=LAMBDA(n,,IFS(i<n,RIFS(n,i+1),1,i))[/XD][/XR][XR][XD]RSW[/XD][XD]=LAMBDA(n,,SWITCH(TRUE,i<n,RSW(n,i+1),i))[/XD][/XR][XR][XD]RSWb[/XD][XD]=LAMBDA(n,,SWITCH(i,3,3,RSWb(n,i+1)))[/XD][/XR]

 

[mgirvin]

Very nice catch, Xlambda!!!!! IF is still great : ) : )

 

[Xlambda]

There was an Excel glitch regarding recursion iterations and here is the proof.
Since I am a heavy user of recursive algorithms, I do periodic checks of recursive limitations in Excel using some quite simple tools, published before.
NI( n)=LAMBDA(n,IF(n=0,"ok",NI(n-1)))
IT( n)=LAMBDA(n,[ i],IF(i=n,"ok",IT(n,i+1)))
This thread, 2 posts ago, back on 26-Aug-24 published their results, being amazed by the returned high values.
this thread post#50
At that time, communicating with @MrExcel , he found out that Calc team had no awareness of any change regarding recursion limits, which was kind of strange.
Recently, runed the tests again and….......seems we are back to the old values like they were before 28-Aug-24
Reopened same workbook, same worksheet used before.
You can still see the high results of same functions frozen at that time, since the cells were not recalculated, and today's results of same functions. Previous limitations are "back" .

Rec All v6.3.5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		previous results (6/7 month ago)					today 26-Aug-24
3
4		NI(n) :count down, single argument function					10,000,000 iterations			IR(n): Infinite recursion
5		=LAMBDA(n,IF(n=0,"ok",NI(n-1)))								=LAMBDA(n,IR(n+1))
6							=NI(10000000)
7		NI(8190)="ok"		max 8190			ok			=IR(1)
8		NI(8191)=#NUM
9										this was created to force a #NUM error
10		IT(n,[ i ]): count up, 2 arguments => less iterations					=IT(10000000)			!!!! Will make Excel to freeze!!!! and still No #NUM error
11		=LAMBDA(n,[ i ],IF(i=n,"ok",IT(n,i+1)))					ok
12
13		IT(5459)="ok"		max 5459
14		IT(5460)=#NUM
15
16		↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
17							results untouched since 26-Aug-2024
18		old limitations are back
19		8190 and 5459					today 02-Jan-2025
20
21		=NI(8190)		=NI(8191)			=NI(10000000)
22		ok		#NUM!			#NUM!		?????????????
23
24		=IT(5459)		=IT(5460)			=IT(10000000)
25		ok		#NUM!			#NUM!		??????????????
26
rec it

Cell Formulas
Range	Formula
G6,D24,B24,G24,D21,B21,G21,G10	G6	=FORMULATEXT(G7)
G7,G22	G7	=NI(10000000)
G11,G25	G11	=IT(10000000)
B22	B22	=NI(8190)
D22	D22	=NI(8191)
B25	B25	=IT(5459)
D25	D25	=IT(5460)

Lambda Functions
Name	Formula
IR	=LAMBDA(n,IR(n+1))
IT	=LAMBDA(n,[ i],IF(i=n,"ok",IT(n,i+1)))
NI	=LAMBDA(n,IF(n=0,"ok",NI(n-1)))
TIMEIT	=LAMBDA(·🕑,ƒ,IF(ROWS(ƒ),TEXT(MOD(NOW()-TODAY()-·🕑,1),"[hh]:mm:ss.000")))
VSrd	=LAMBDA(n,a,REDUCE(a,SEQUENCE(n),LAMBDA(v,i,VSTACK(v,i))))
VSrs	=LAMBDA(n,a,[ i],LET(i,i+1,IF(i=n,a,VSrs(n,VSTACK(a,i),i))))

 

[mgirvin]

Just like I alwasy knew: You are smarter than the whole team of smart people on the Excel Team : ) : ) : ) Truth!