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 : ) : )