ARF

=ARF(a,ai,i)

a
(a1,a2,...,an) non-adjacent ranges/arrays on same sheet, enclosed in parentheses
ai
always omitted (vector carrier)
i
always omitted (vector carrier)

DIY Array Recursive Function kit

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
260
Office Version
  1. 365
Platform
  1. 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))))
AGG study.xlsx
ABCDEFGHIJKLMNOPQRS
1Base Case example (simplest case scenario): append arrays (single cell , or 1D horizontal arrays)
2Note: 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
3To append 2D arrays we already have APPENDNHV
4Writing the recursive function following the syntax draft, function name, let's define APP:
5APP(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))))
6The appending "engine" functionality is extremely simple IF(s=j,x,ai)
7Is equivalent with this :
8=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))
9a
10ba1=APP((C11:D11,C14:E14,C17:D17),,)
112a2a2#N/A
12b34
13a212#N/A
14b34
15
16a3
1712
18
19General Case example: let's consider a more complex F(x) that sorts in ascending order the unique elements of an array
20a1
21a23=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))
22xw223atwx
23txa
24
25so F(x) will be F(x)=TRANSPOSE(SORT(UNIQUE(AFLAT(x))))
26Now let's define our specific recursive function (ASU) using the kit syntax
27ASU(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))))
28a2
29a2-1=ASU((B21:D23,B29:D33,B36:C37),,)
30qac23atwx
31dc2-123acdq
32-13-12q
332dd
34
35a3Other function on minisheet
36q2AFLAT
372q
38
39This 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.
40It will be nice to see others function creations posted here!!!
41
ARF post
Cell Formulas
RangeFormula
A8,G29,G21,G10A8=FORMULATEXT(A9)
A9:A11A9=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))
G11:I13G11=APP((C11:D11,C14:E14,C17:D17),,)
G22:L22G22=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))
G30:M32G30=ASU((B21:D23,B29:D33,B36:C37),,)
Dynamic array formulas.
 
Upvote 0

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,571
FYI: Another video from MrExcel about the new functions:
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
260
Office Version
  1. 365
Platform
  1. Windows
FYI: Another video from MrExcel about the new functions:
Hi, kind of off-topic here, but I think it worth mentioning, wrote the first lambda ( AGGR AGGR(a,fn,[kn]) with an optional argument "[kn]" and I have noticed that array syntax field, (very first row) does not accept brackets.
Function arguments field works fine, took the brackets. ✌
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,571
Hi, kind of off-topic here, but I think it worth mentioning, wrote the first lambda ( AGGR AGGR(a,fn,[kn]) with an optional argument "[kn]" and I have noticed that array syntax field, (very first row) does not accept brackets.
Function arguments field works fine, took the brackets. ✌
Thanks for the heads up. I just made the necessary changes to allow brackets and edited the AGGR function post.
 

Forum statistics

Threads
1,144,627
Messages
5,725,371
Members
422,621
Latest member
dfrare

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top