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

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
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
More examples will follow .... increasing in complexity...but with help of ARF kit template....everything is simple and straight forward
AGG study.xlsx
ABCDEFGHIJKLMNOPQRS
1Task 1: Extract the top k largest unique values of each array of an array "range" (no duplicates)
2
3samplestep 1: study and create our custom made F(x,k) for a single array using LARGE
41LARGE does not exclude duplicatesTo exclude duplicates we need UNIQUE
52=LARGE(A4:A10,SEQUENCE(3))=LARGE(UNIQUE(A4:A10),SEQUENCE(3))
6355
7344
8443
94
1052D sample=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(3))
11a45
12344since we need horizontal arrays, SEQUENCE(,3) will do the trick
1333=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(,3))
14b5543
154#N/Aour F(x,k)=LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k))
16(mixed data,blanks,numeric,text,errors)
17(AFLAT filters everything)
18
19step 2: replace F(x,k) in the ARF kit template with LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k)) and define the new function
20(also always check that all the arguments trough the syntax are consistent, no variable names duplicates..etc)
21ARF function kit: 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))))
22Now we can define our specific recursive function for the task: ALARGE
23 ALARGE(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,ALARGE(a,k,IF(s=j,LARGE(UNIQUE(AFLAT(x)),SEQUENCE(,k)),ai),j-1))))
24
25a1=ALARGE((B26:D29,B31:E33,B35:E39),2,,)
26536k,276Imp Note:
2742g54A recursive function will work fine also for single arrays
28d5698=ALARGE(B35:E39,5,,)
293k798765
30a2=ALARGE((B26:D29,B31:E33,B35:E39),3,,)
31w235k,3765
32432-1543
3302zx987
34a3
357b5y=ALARGE((B26:D29,B31:E33,B35:E39),4,,)
36w659k,47654
3797585432
387d489876
392qwk
40
ARF post 2
Cell Formulas
RangeFormula
C5,G5,H35,H30,N28,H25,I13,G10C5=FORMULATEXT(C6)
C6:C8C6=LARGE(A4:A10,SEQUENCE(3))
G6:G8G6=LARGE(UNIQUE(A4:A10),SEQUENCE(3))
G11:G13G11=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(3))
I14:K14I14=LARGE(UNIQUE(AFLAT(C11:D15)),SEQUENCE(,3))
D15D15=NA()
H26:I28H26=ALARGE((B26:D29,B31:E33,B35:E39),2,,)
N29:R29N29=ALARGE(B35:E39,5,,)
H31:J33H31=ALARGE((B26:D29,B31:E33,B35:E39),3,,)
H36:K38H36=ALARGE((B26:D29,B31:E33,B35:E39),4,,)
Dynamic array formulas.
 
AGG study.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Task 2: Integrate AGGREGATE function in a recursive function , for an array "range" (fn argument 1-13) (no k argument)
2will use ARF function kit template:
3ARF(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))))
4Let's define ARF=AGG for F(x)=F(fn,o,x)=AGGREGATE(fn,o,x)
5paying attention to the argument distribution consistency trough the function will get to:
6AGG(a,fn,o,ai,i)=LAMBDA(a,fn,o,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,AGG(a,fn,o,IF(s=j,AGGREGATE(fn,o,x),ai),j-1))))
7a1a2a3a4AGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )
8536w2357b5yt-3yh-2function_num arg.options argument
942g432-1w6-1-1h4l54↓function↓values to be ignored
10d5602zx-22.5-585h1-331Average0Ignore nested Subtotal & Aggregate functions
113k73d4-331-25k2Count1Ignore hidden rows and nested Subtotal & Aggregate functions
122qwk3Counta2Ignore error values and nested Subtotal & Aggregate functions
134Max3Ignore hidden rows, error values and nested Subtotal & Aggregate functions
14o=65Min4Ignore nothing
15fn123456789101112136Product5Ignore hidden rows
164.6912724536001.71.6412.82.5557Stdev.S6Ignore error values
172.29125-101.91.7203.43.1228Stdev.P7Ignore hidden rows and error values
18213208-5-30240004.13.92617152.5-19Sum
191.613205-36480003.23219.99.23510Var.S
20=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),D15,6,,)11Var.P
2112Median
22single cell formula for all fn13Mode.Sngl
23=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),SEQUENCE(,13),6,,)14Largefn 14-19 with k argument, next post, creating AGGK recursive function
244.6912724536001.71.6412.82.55515Small
252.29125-101.91.7203.43.12216Percentile.Inc
26213208-5-30240004.13.92617152.5-117Quartile.Inc
271.613205-36480003.23219.99.23518Percentile.Exc
2819Quartile.Exc
29if we want only min,max and sum, in a single cell formula fn={5,4,9}
30=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),{5,4,9},6,,)
312741
32-1520
33-5826
34-3521
35
AGG post
Cell Formulas
RangeFormula
D16:P19D16=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),D15,6,,)
D20D20=FORMULATEXT(D16)
D23,D30D23=FORMULATEXT(D24)
D24:P27D24=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),SEQUENCE(,13),6,,)
D31:F34D31=AGG(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),{5,4,9},6,,)
Dynamic array formulas.
 
AGG study.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Task 3: Integrate AGGREGATE function in a recursive function , for an array "range" (fn argument 14-19) (with k argument)
2will use ARF function kit template:
3ARF(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))))
4Let's define ARF=AGGK for F(x)=F(fn,o,x,k)=AGGREGATE(fn,o,x,k)
5paying attention to the argument distribution consistency trough the function will get to:
6AGGK(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,AGGK(a,fn,o,k,IF(s=j,AGGREGATE(fn,o,x,k),ai),j-1))))
7a1a2a3a4AGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )
8536w2357b5yt-3yh-2function_num arg.options argument
942g432-1w6-1-1h4l54↓function↓values to be ignored
10d5602zx-22.5-585h1-331Average0Ignore nested Subtotal & Aggregate functions
113k73d4-331-25k2Count1Ignore hidden rows and nested Subtotal & Aggregate functions
122qwk3Counta2Ignore error values and nested Subtotal & Aggregate functions
13o=64Max3Ignore hidden rows, error values and nested Subtotal & Aggregate functions
14k={1,3}k={1,2}k={0,0.2,0.7,1}k={0,1,2,3,4}5Min4Ignore nothing
15fn=14fn=15fn=16fn=176Product5Ignore hidden rows
167623235.67235677Stdev.S6Ignore error values
1753-10-11.235-122358Stdev.P7Ignore hidden rows and error values
1886-5-3-5-1.64.48-5-12.5589Sum
1955-3-3-3-245-3-234510Var.S
2011Var.P
21k={0.1,0.2,0.7,0.9}k={1,2,3}12MedianThe functions that require argument k
22fn=18fn=1913Mode.Snglfunctionmeaning of k
23236735614Large14LargeReturn the k'th largest value
24-1035123.515Small15SmallReturn the k'th smallest value
25-4.2-2.24.87.6-1.52.55.516Percentile.Inc16Percentile.IncReturn the k'th percentile0<=k<=1
26-3-2.245-234.517Quartile.Inc17Quartile.IncReturn the k'th quartile0<=k<=4(<5)
2718Percentile.Exc18Percentile.ExcReturn the k'th percentile0<k<1
28 all FORMULATEXT syntax=AGGK((a1,a2,a3,a4),fn,o,k,,)19Quartile.Exc19Quartile.ExcReturn the k'th quartile1<=k<=3(<4)
29
AGGK post
Cell Formulas
RangeFormula
A16:B19A16=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),14,6,{1,3},,)
D16:E19D16=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),15,6,{1,2},,)
G16:J19G16=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),16,6,{0,0.2,0.7,1},,)
L16:P19L16=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),17,6,{0,1,2,3,4},,)
G23:J26G23=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),18,6,{0.1,0.2,0.7,0.9},,)
L23:N26L23=AGGK(($A$8:$C$11,$E$8:$H$10,$J$8:$M$12,$O$8:$S$11),19,6,{1,2,3},,)
Dynamic array formulas.
 
AGG study.xlsx
ABCDEFGHIJKLMNOPQRSTU
1a1Task 4 : a. Extract 2nd smallest and 2nd largest of each array of an array "range"
2536o=6,fn={14,15},k=2
342g=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15},6,2,,)
4d5663
53k740b. Extract Percentile.Inc and Percentile.Exc values for 10 and 90% , for each array of an array range
6a27-3o=6,fn={16,18},k=0.1
7w2355-3=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.1,,)
8432-12.82
902zx-0.2-1o=6,fn={16,18},k=0.9
10a3-2.8-4.2=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.9,,)
117b5y-2.8-36.27
12w6-1-14.25
13-22.5-586.87.6
143d4-355
152qwk
16a4c. Extract 1st and 3rd Quartile.Inc and Quartile.Exc values for each array of an array "range"
17t-3yh-2o=6,fn={17,19},k=1
18h4l54=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,1,,)
195h1-3333
2031-25k21o=6,fn={17,19},k=3
21-1-1.5=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,3,,)
22-2-266
2333.5
2455.5
2544.5
26
AGGK post 2
Cell Formulas
RangeFormula
G3,M21,J18,M10,J7G3=FORMULATEXT(G4)
G4:H7G4=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15},6,2,,)
J8:K11J8=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.1,,)
M11:N14M11=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{16,18},6,0.9,,)
J19:K22J19=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,1,,)
M22:N25M22=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{17,19},6,3,,)
Dynamic array formulas.
 
AGG study.xlsx
ABCDEFGHIJKLMNOPQRST
1a1Task 5: All task 4 ops in a single cell
2536
342go=6,fn={14,15,16,18,16,18,17,19,17,19},k={2,2,0.1,0.1,0.9,0.9,1,1,3,3}
4d56
53k7=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15,16,18,16,18,17,19,17,19},6,{2,2,0.1,0.1,0.9,0.9,1,1,3,3},,)
6a2632.826.273366
7w23540-0.2-14.252133.5
8432-17-3-2.8-4.26.87.6-1-1.555.5
902zx5-3-2.8-355-2-244.5
10a3
117b5y
12w6-1-1
13-22.5-58
143d4-3
152qwk
16a4
17t-3yh-2
18h4l54
195h1-33
2031-25k
21
AGGK post 3
Cell Formulas
RangeFormula
G5G5=FORMULATEXT(G6)
G6:P9G6=AGGK((A2:C5,A7:D9,A11:D15,A17:E20),{14,15,16,18,16,18,17,19,17,19},6,{2,2,0.1,0.1,0.9,0.9,1,1,3,3},,)
Dynamic array formulas.
 
AGG study.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1a1Task 6: Function_num argument 13 of AGGREGATE function is for MODE.SNGL
2536Let's create a recursive function that is able to calculate MODE.MULT for each array of an array "range"
342gNote: MODE.MULT is not among the functions covered by AGGREGATE because it can deliver an array result, but our function kit design can handle this with ease, so we will cover it.
4d56step 1:=MODE.MULT(A2:C5)=TRANSPOSE(MODE.MULT(A2:C5))
53k7Identify a formula F(x) for a single array5536
6a23
7w2356
8432-1
90-1zxsince ARF formula kit template is designed to handle 1D horiz arrays, our F(x) will be:
10a3F(x)=TRANSPOSE(MODE.MULT(x))
117b5y
12w6-1-1step 2:
13-22.5-58define new recursive function AMM, using the kit template
143d4-3ARF(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))))
156qwk
16a4AMM(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,AMM(a,IF(s=j,TRANSPOSE(MODE.MULT(x)),ai),j-1))))
17t-3yh-2=AMM((A2:C5,A7:D9,A11:D15,A17:E20),,)
18h4l54536
195h1-3323-1
2031-25k6-1#N/A
2155#N/A
22
23To handle the NA error , since the result vector carrier is ai, we modify in formula ai with IFERROR(ai,"") (after IF(j=0,ai,AMM(…. )
24AMM(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,""),AMM(a,IF(s=j,TRANSPOSE(MODE.MULT(x)),ai),j-1))))
25=AMMnoER((A2:C5,A7:D9,A11:D15,A17:E20),,)
26536
2723-1Note: To visualize the 2 behaviors at once , with errors and no errors, we needed 2 dif functions, that’s why we have AMMnoER
286-1
2955
30
AMM
Cell Formulas
RangeFormula
L4,O4,G25,G17L4=FORMULATEXT(L5)
L5:L7L5=MODE.MULT(A2:C5)
O5:Q5O5=TRANSPOSE(MODE.MULT(A2:C5))
G18:I21G18=AMM((A2:C5,A7:D9,A11:D15,A17:E20),,)
G26:I29G26=AMMnoER((A2:C5,A7:D9,A11:D15,A17:E20),,)
Dynamic array formulas.
 
Task 7: Wrap up all AGGREGATE related functions (AGG,AGGK,AMM) functionality in "all in one" tool function T_AGG(a,fn,o,k,ai,i) !! recursive !!
So far, is the first explicit function here based on ARF kit template apart of all intermediate "study" ones. The possibilities are limitless . Choose AGGREGATE functionality because really is a function that does a lot, and attach to it recursive array "range" versatility could be an useful tool. Hope that nobody will hate me for so many posts, it's only a fine journey that I think, deserves to be shared.
Excel Formula: 
=LAMBDA(a,fn,o,k,ai,i,
    LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),
       IF(j=0,IFERROR(ai,""),
          T_AGG(a,fn,o,k,IF(s=j,
             IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values"),
               ai),j-1))
    )
)
AGG study.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1a1Task 7: Wrap up all AGGREGATE related functions (AGG,AGGK, AMM) functionality in one function (T_AGG)
2536step 1 : Identify F(x,fn,o,k) , needs to have all arguments possible in AGGREGATE, and according to fn argument 1<=fn<=13 or 14<=fn<=19 proper AGGREGATE forms are called (with k or without k)
342gAlso we added MODE.MULT functionality for a new fn value fn=20. All this in a simple IFS formula.
4d56Note: if fn<>(14,19), even if k has a value , will be ignored because the formula calls the AGGREGATE without k form.
53k7F(x,fn,o,k)=IFS(AND(fn>=1,fn<=13),AGGREGATE(fn,o,x),AND(fn>=14,fn<=19),AGGREGATE(fn,o,x,k),fn=20,TRANSPOSE(MODE.MULT(x)),TRUE,"check values")
6a2step 2: we create the tool recursive function using the kit template, as above (T_AGG)
7w235o=6k,ignored
8432-1fn1234567891011121320
90-1zx4.556912724536001.6671.571412.7782.46955536
10a31.8899125-102.1472.025174.6114.0992223-1
117b5y2.26913208-5-90720004.2264.0629.517.8616.49366-1
12w6-1-11.61513205-36480003.153.027219.9239.163555
13-22.5-58=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),I8,6,,,)↑
143d4-3=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),W8,6,,,)
156qwkk220.220.22(fn 20 is in the upper section because does not need k argument)
16a4fn141516171819
17t-3yh-2633535
18h4l544-1-0.42-12
195h1-337-3-1.63-2.23
2031-25k5-3-23-2.23
21=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),H16,6,H15,,)
22
23single cells formulas
24=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,13),6,,,)
254.556912724536001.6671.571412.7782.46955
261.8899125-102.1472.025174.6114.09922
272.26913208-5-90720004.2264.0629.517.8616.4936
281.61513205-36480003.153.027219.9239.1635
29
30=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,6,14,1),6,{2,2,0.2,2,0.2,2},,)
31633535
324-1-0.42-12
337-3-1.63-2.23
345-3-23-2.23
35
T_AGG
Cell Formulas
RangeFormula
W9:Y12,I9:U12I9=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),I8,6,,,)
I13,H21I13=FORMULATEXT(I9)
U14U14=FORMULATEXT(W9)
H17:M20H17=T_AGG(($A$2:$C$5,$A$7:$D$9,$A$11:$D$15,$A$17:$E$20),H16,6,H15,,)
I24,H30I24=FORMULATEXT(I25)
I25:U28I25=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,13),6,,,)
H31:M34H31=T_AGG((A2:C5,A7:D9,A11:D15,A17:E20),SEQUENCE(,6,14,1),6,{2,2,0.2,2,0.2,2},,)
Dynamic array formulas.
 
Task 8: Filter,Unique,Sort an array "range", into an array of rows, 1 row for each array, create a !!recursive!! tool T_FUS(a,f,u,s,ai,i), based on ARF kit template. Calls AFLAT
a: single array or array "range" (a1,a2...an) ( All recursive functions created with the kit work fine for a single array or an array "range")
f: filter argument, 0, filters out only blanks/null strings,-1,filters text, 1, filters numeric values
u: unique argument, 0, does nothing, 1, extracts unique values
s: sort argument, 0, no sorting, 1, ascending order, -1,descending order
ai,i: arguments carriers of the recursive functionality, always ignored
Note: The order of operations triggered by the arguments is from left to right, or as an inside out representation SORT(UNIQUE(FILTER(..)))
Excel Formula: 
=LAMBDA(a,f,u,s,ai,i,
    LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),
       IF(j=0,IFERROR(ai,""),T_FUS(a,f,u,s,IF(q=j,
           LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")),
              ai),j-1))
    )
)
AGG study.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Task 8: Filter,Unique,Sort an array "range", into an array of rows, 1 row for each array, create a recursive tool T_FUS(a,f,u,s,ai,i)
2
3step 1: identifying arguments and their values
4fus
5-10-1a1a2a3a4
6010536w2357b5yt-3yh-2
71142g432-1w6-1-1h4l4
8textno uniquedescendingd560-1zx-22.585h1-33
9no blanksuniqueno sort3k73d-331-25k
10numericascending6qwk
11
12step 2: Identify F(x,f,u,s) for a single array
13 =LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))
14check functionality for a3 array
15=LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))(R6:U10,1,1,1)
16-3-2-12.535678
17F(x,f,u,s) will be whatever comes after LET, we do this to save defining another sepparate lambda, the variables will be predifined beforehand by the main function in the kit structure
18F(x,f,u,s)=LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values"))
19
20step 3: create the tool lambda using ARF recursive function kit template
21ARF(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))))
22As I said before, we have to check consistency of variables and arguments when using the kit
23We notice that the kit has "s" variable already , and the F(x) has an "s" argument (sort , so we have to change that, we change the varable "s" in the kit with "q"
24Also we replace ai, final vector that carries the result with IFERROR(ai,""), to handle eventual NA errors
25ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ARF(a,IF(q=j,F(x),ai),j-1))))
26Now replacing F(x) will get to T_FUS, our function for the task:
27T_FUS(a,f,u,s,ai,i)=LAMBDA(a,f,u,s,ai,i,LET(n,AREAS(a),q,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),T_FUS(a,f,u,s,IF(q=j, LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")),ai),j-1))))
28Note: LET inside LET is very versatile
29f,0,u,0,s,0 (no blanks,no unique,no filter) (remove blanks,rest unchanged)
30=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),,,,,)
3153642gd563k7
32w235432-10-1zx
337b5yw6-1-1-22.583d-36qwk
34t-3yh-2h4l45h1-3331-25k
35
36f,1,u,1,s,1 (unique numbers ascending order)f,-1,u,1,s,-1 (unique text descending order)
37=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),1,1,1,,)=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),-1,1,-1,,)
38234567kgd
39-102345zxw
40-3-2-12.535678ywqkdb
41-3-21345ytlkh
42
43If filter has no results example
44a1a2=T_FUS((B45:C46,E45:F46),1,1,1,,)=T_FUS((B45:C46,E45:F46),-1,1,1,,)
45ab12 abc
46ca31123
47
48If you want other value than null string spot "v" variable inside formula , after second LET …v,FILTER(y,z,"")…and change "" , for example with "not found"
49
50If wrong argument value
51
52=T_FUS((B45:C46,E45:F46),1,2,1,,)
53check values
54check values
55
T_FUS
Cell Formulas
RangeFormula
A15,A52,P44,I44,L37,B37,B30A15=FORMULATEXT(A16)
A16:I16A16=LAMBDA(x,f,u,s,LET(y,AFLAT(x),z,SWITCH(f,0,y<>"",-1,ISTEXT(y),1,ISNUMBER(y)),v,FILTER(y,z,""),w,SWITCH(u,0,v,1,UNIQUE(v)),t,SWITCH(s,0,w,-1,SORT(w,,-1),1,SORT(w)),IFNA(TRANSPOSE(t),"check values")))(R6:U10,1,1,1)
B31:T34B31=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),,,,,)
B38:J41B38=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),1,1,1,,)
L38:Q41L38=T_FUS((I6:K9,M6:P8,R6:U10,W6:AA9),-1,1,-1,,)
I45:K46I45=T_FUS((B45:C46,E45:F46),1,1,1,,)
P45:R46P45=T_FUS((B45:C46,E45:F46),-1,1,1,,)
A53:A54A53=T_FUS((B45:C46,E45:F46),1,2,1,,)
Dynamic array formulas.
 
Last edited:
A1RF !! recursive !! DIY Array 1 Recursive Function kit (Single Array Recursive Function kit)
A1RF(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE( n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1RF(a,d,IF(s=j,F(x),ai),j-1))))
a: array
d: "direction" argument, 0 or ignored, horizontal (by rows), 1, vertical (by columns)
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
Task 9: If we were able to write recursive functions for n arrays let's write a function kit for a single (one) array that has n rows/columns
AGG study.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1Task 9: If we were able to write recursive functions for n arrays let's write a function kit for a single (one) array that has n rows/columns
2Since an array can have 2 dimensions will define 2 kits A1RFH and A1RFV (array one recursive function kit horizontal/vertical) or a single function A1RF for both "directions"(extra arg. "d")
39.1A1RFH
4We will still use universal ARF function kit template structure:
5ARF(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))))
6we replace:
7nr.arrays n,AREAS(a) with nr.rows n,ROWS(a)
8x,INDEX(a,,,j) extracts from the "range" (a1,a2….aj…an) one array at a time for every iteration in the recursive cycle
9will replace it with x,INDEX(a,j,) extracts one full row at a time for every iteration operation in the recursive cycle
10A1RFH(a,ai,i)=LAMBDA(a,ai,i,LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,A1RFH(a,IF(s=j,F(x),ai),j-1))))
11
129.2A1RFV
13we replace:
14nr.arrays n,AREAS(a) with nr.columns n,COLUMNS(a)
15x,INDEX(a,,,j) extracts from the "range" (a1,a2….aj…an) one array at a time for every iteration in the recursive cycle
16will replace it with x,INDEX(a,,j) extracts one full column at a time for every iteration operation in the recursive cycle
17also we have to change the orientation of the building result engine
18will replace s,SEQUENCE(n) with s,SEQUENCE(,n)
19A1RFV(a,ai,i)=LAMBDA(a,ai,i,LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,A1RFV(a,IF(s=j,F(x),ai),j-1))))
20Note: As a general note, to change the direction or orientation of an array outcome we can also use double transpose.
21A1RFV(a)=TRANSPOSE(A1RFH(TRANSPOSE(a))
22
239.3A1RF
24We noticed so far that second part of the functions (the building result engine, the ai argument) always stays the same, regardless the orientation
25IF(s=j,F(x),ai). This means, we can introduce a separate argument for orientation/direction , d, with 2 values to cover both scenarios
26d: 0 or ignored for horizontal (by rows), 1, for vertical (by columns)
27A1RF(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE(n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1RF(a,d,IF(s=j,F(x),ai),j-1))))
28
29Let's put this to test for a simple operation, SUM, function name A1SUM using the kit template so, F(x)=SUM(x)
30A1SUM(a,d,ai,i)=LAMBDA(a,d,ai,i,LET(n,IF(d,COLUMNS(a),ROWS(a)),s,IF(d,SEQUENCE(,n),SEQUENCE(n)),j,IF(i="",n,i),x,IF(d,INDEX(a,,j),INDEX(a,j,)),IF(j=0,ai,A1SUM(a,d,IF(s=j,SUM(x),ai),j-1))))
31
32sampled=0check
33=SEQUENCE(6,4)=A1SUM(B34#,,,)=SUM(B34:E34)
3412341010
3556782626
3691011124242
37131415165858
38171819207474
39212223249090
40
41d=1
42=A1SUM(B34#,1,,)
4366727884
44check
45=SUM(B34:B39)
4666727884
47
A1RF post
Cell Formulas
RangeFormula
B33,B45,B42,G33,J33B33=FORMULATEXT(B34)
B34:E39B34=SEQUENCE(6,4)
G34:G39G34=A1SUM(B34#,,,)
J34:J39J34=SUM(B34:E34)
B43:E43B43=A1SUM(B34#,1,,)
B46:E46B46=SUM(B34:B39)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

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
Back
Top