Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 860
- Office Version
- 365
- Platform
- Windows
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))))
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. |
Upvote
0