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

Board Regular
Joined
Mar 8, 2021
Messages
224
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

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
Task 10: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGH (horizontal/by rows) !! recursive !! T_A1GGH(a,fn,o,k,ai,i)
Excel Formula:
=LAMBDA(a,fn,o,k,ai,i,
    LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,
      T_A1GGH(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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1Task 10: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGH (horizontal/by rows)
2Following the same reasoning as in Task 7 post, we already have identified F(x,fn,o,k)
3F(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")
4Using A1RFH kit.
5A1RFH(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))))
6Will get to:
7T_A1GGH(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,ROWS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,j,),IF(j=0,ai,T_A1GGH(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))))
8
9o,6,k,ignoredAGGREGATE functionality: AGGREGATE( function_num, options, array, [k] )
10samplefn1234567891011121320function_num arg. "fn"options argument "o"
11122a4473.3367714482.161.97204.673.893224functionvalues to be ignored
128bb814c141147148125443.463441291188141Average0Ignore nested Subtotal & Aggregate functions
13d3535d446532251.151161.33143352Count1Ignore hidden rows and nested Subtotal & Aggregate functions
1466g8g87468623041.151281.33176683Counta2Ignore error values and nested Subtotal & Aggregate functions
1599595576695911252.192424.8479954Max3Ignore hidden rows, error values and nested Subtotal & Aggregate functions
16d3a3443.546431440.580.5140.330.253.53345Min4Ignore nothing
17=T_A1GGH($A$11:$G$16,J10,6,,,)=T_A1GGH($A$11:$G$16,X10,6,,,)6Product5Ignore hidden rows
187Stdev.S6Ignore error values
19o,68Stdev.P7Ignore hidden rows and error values
20k220.220.229Sum
21fn14151617181910Var.S
2242231.4311Var.P
2314881181112MedianThe functions that require argument k
2453343413Mode.Snglfunctionmeaning of k
2586676714Large14LargeReturn the k'th largest value
2695575715Small15SmallReturn the k'th smallest value
274333.533.516Percentile.Inc16Percentile.IncReturn the k'th percentile0<=k<=1
28=T_A1GGH($A$11:$G$16,J21,6,J20,,)17Quartile.Inc17Quartile.IncReturn the k'th quartile0<=k<=4(<5)
2918Percentile.Exc18Percentile.ExcReturn the k'th percentile0<k<1
30single cell formula fn,{1,2,…,13},o,6,k,ignored19Quartile.Exc19Quartile.ExcReturn the k'th quartile1<=k<=3(<4)
31=T_A1GGH(A11:G16,SEQUENCE(,13),6,,,)custom created20Mode.Mult
323.3367714482.161.97204.673.8932
331147148125443.46344129118
34446532251.151161.33143
357468623041.151281.33176
3676695911252.192424.8479
373.546431440.580.5140.330.253.53
38
39single cell formula fn,{14,15,16,17,18,19},o,6,k,{2,2,0.2,2,0.2,2}single cell formula for multiple k arg.
40=T_A1GGH(A11:G16,{14,15,16,17,18,19},6,{2,2,0.2,2,0.2,2},,)=T_A1GGH(A11:G16,14,6,{1,2,3},,)
4142231.43744
4214881181114148
43533434553
44866767886
45955757999
464333.533.5443
47
A1RF post 2
Cell Formulas
RangeFormula
J10:V10J10=SEQUENCE(,13)
X11:Y16,J11:V16J11=T_A1GGH($A$11:$G$16,J10,6,,,)
J17,J28J17=FORMULATEXT(J11)
U17U17=FORMULATEXT(X11)
J22:O27J22=T_A1GGH($A$11:$G$16,J21,6,J20,,)
J31,V40,J40J31=FORMULATEXT(J32)
J32:V37J32=T_A1GGH(A11:G16,SEQUENCE(,13),6,,,)
J41:O46J41=T_A1GGH(A11:G16,{14,15,16,17,18,19},6,{2,2,0.2,2,0.2,2},,)
V41:X46V41=T_A1GGH(A11:G16,14,6,{1,2,3},,)
Dynamic array formulas.
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
Task 11: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGV (vertical/by columns) !! recursive !! T_A1GGV(a,fn,o,k,ai,i)
Excel Formula:
=LAMBDA(a,fn,o,k,ai,i,
    LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,
      T_A1GGV(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,MODE.MULT(x),TRUE,"check values"),
          ai),j-1))
    )
)
AGG study.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Task 11: To build the equivalent of tool lambda T_AGG (Task 7) , this time for single arrays T_A1GGV (vertical/by columns)
2Following the same reasoning as in Task 7 post, we already have identified F(x,fn,o,k)
3F(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")
4Taking in consideration the orientation we also have to modify TRANSPOSE(MODE.MULT(x)) to MODE.MULT(x)
5F(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,MODE.MULT(x),TRUE,"check values")
6Using A1RFV kit:
7A1RFV(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))))
8Will get to:
9 T_A1GGV(a,fn,o,k,ai,i)=LAMBDA(a,fn,o,k,ai,i,LET(n,COLUMNS(a),s,SEQUENCE(,n),j,IF(i="",n,i),x,INDEX(a,,j),IF(j=0,ai,T_A1GGV(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,MODE.MULT(x),TRUE,"check values"),ai),j-1))))
10
11sampleo,6,k,ignoredo,6
1218d69dfn=T_A1GGV($A$12:$F$18,H13,6,,,)fnk=T_A1GGV($A$12:$F$18,P13,6,Q13,,)
132b69313.333114773.51424145894
142b3ga2644464152283653
15a85853776666160.2283653
16414393471458941723114773.5
174c5g545183653180.21.483653
18714d8546448125442252304911251441923114773.5
1972.163.4641.1551.1552.1910.577
2081.97231120.5
219204416284214
22104.667121.3331.3334.80.333
23113.88991140.25
24123114773.5
2513283693
26=T_A1GGV($A$12:$F$18,H27,6,,,)
2720283693
284145854
29
30single cell formula fn,{1;2;…;13},o,6,k,ignored
31(fn multiple argument array should follow rows constantant array syntax (";" separator instead of ",")
32=T_A1GGV(A12:F18,SEQUENCE(13),6,,,)
333.333114773.5single cell formula for multiple k arguments
34644464=T_A1GGV(A12:F18,14,6,{1;2;3},,)
357766667145894
3671458944145894
37183653483693
3844812544225230491125144
392.163.4641.1551.1552.1910.577
401.97231120.5
41204416284214
424.667121.3331.3334.80.333
433.88991140.25
443114773.5
45283693
46
A1RF post 3
Cell Formulas
RangeFormula
I12,P34,I32,I26,R12I12=FORMULATEXT(I13)
R13:W18R13=T_A1GGV($A$12:$F$18,P13,6,Q13,,)
I27:N28,I13:N25I13=T_A1GGV($A$12:$F$18,H13,6,,,)
I33:N45I33=T_A1GGV(A12:F18,SEQUENCE(13),6,,,)
P35:U37P35=T_A1GGV(A12:F18,14,6,{1;2;3},,)
Dynamic array formulas.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,549
@Xlambda / @schardt679

Did you get the new functions in the latest update?

They are very good improvements. Although I didn't have a chance to play with them in detail, I can say that I already love MAP, BYs, and REDUCE.
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
@Xlambda / @schardt679

Did you get the new functions in the latest update?

They are very good improvements. Although I didn't have a chance to play with them in detail, I can say that I already love MAP, BYs, and REDUCE.
Wow, amazing, I have them!!! Did not knew. Thanks for letting us know. 😊✌✌ Functions designed with LAMBDA integration, wow, this is a huge step forward, don't you think??!! I love it.
Great statement from Microsoft, seems like LAMBDA has a great future. See ? Looks like Microsoft is reading my mind 😂 Exactly these type functionalities are covered in this very post, with help of recursion versatility, and also I have addressed these with lot of other functions from the beginning like AAGREGATE etc. Means we are rowing the same boat. Can wait to dive into them. Probably a lot of functions here will be rewritten. Always my goal was to write few rows , short but powerful formulas. If a formula or function is not short, means we did not get to the essence of it. Now looks that will have tools to make them even shorter. Great fun is coming. Thanks again and kudos to Microsoft!!
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,549

ADVERTISEMENT

Wow, amazing, I have them!!! Did not knew. Thanks for letting us know. 😊✌✌ Functions designed with LAMBDA integration, wow, this is a huge step forward, don't you think??!! I love it.
Great statement from Microsoft, seems like LAMBDA has a great future. See ? Looks like Microsoft is reading my mind 😂 Exactly these type functionalities are covered in this very post, with help of recursion versatility, and also I have addressed these with lot of other functions from the beginning like AAGREGATE etc. Means we are rowing the same boat. Can wait to dive into them. Probably a lot of functions here will be rewritten. Always my goal was to write few rows , short but powerful formulas. If a formula or function is not short, means we did not get to the essence of it. Now looks that will have tools to make them even shorter. Great fun is coming. Thanks again and kudos to Microsoft!!
(y)

These are really good improvements. I can't wait to play with them.
 

schardt679

Board Regular
Joined
Mar 27, 2021
Messages
56
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
I did get them. I'll have to play around with them. There's not a lot of info on them yet. This is huge. I think it will help make currently long LAMBDAs a lot shorter. It will be cool to mix BYCOL, BYROW, SCAN, MAP, REDUCE, MAKEARRAY, and ISOMITTED to rewrite existing LAMBDAs and incorporate them into new ones.
 

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Very first glimpse of new function BYROW
AGG study.xlsx
ABCDEFGHIJKLMNO
1Very first glimpse on new!! BYROW function
2like in MrExcel video
3=BYROW(A4:C5,LAMBDA(a,MAX(a)))
41323
54255
6works fine
7Seems that any function that returns a single result, applied to every row of an array , will produce an array of results accordingly
8=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,2)))
92
104
11second largest value of each row, also works because returns a single value
12
13=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,{1,2})))
14#CALC!
15top 2 largest values of each rows does not work, because it does not return a single result
16k argument of AGGREGATE {1,2} is forcing an array result, not a single result
17{1;2} does not work either, obviously
18
19=T_A1GGH(A4:C5,14,6,{1,2},,)
2032
2154
22my function, (Task 11 of this post) can handle this, means that I still have a job 😂
23
24=BYROW(A4:C5,LAMBDA(a,SORT(a)))
25#CALC!
26SORT also does not work , array result
27I will make one that does 😉✌
28
BYCOL,BYROW
Cell Formulas
RangeFormula
E3,E24,E19,E13,E8E3=FORMULATEXT(E4)
E4:E5E4=BYROW(A4:C5,LAMBDA(a,MAX(a)))
E9:E10E9=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,2)))
E14E14=BYROW(A4:C5,LAMBDA(a,AGGREGATE(14,6,a,{1,2})))
E20:F21E20=T_A1GGH(A4:C5,14,6,{1,2},,)
E25E25=BYROW(A4:C5,LAMBDA(a,SORT(a)))
Dynamic array formulas.
 
Last edited:

schardt679

Board Regular
Joined
Mar 27, 2021
Messages
56
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Also as part of this update, it seems when working with dates and one is returned, Excel automatically updates the cell using the source date formatting

It seems it only does it for the first date in an array.

Using the MAKEARRAY function should help with the multiple answer for each row/column problem
 
Last edited:

Xlambda

Board Regular
Joined
Mar 8, 2021
Messages
224
Office Version
  1. 365
Platform
  1. Windows
I wish but I don't think so, since specific arguments rows and columns are required
Book2
ABCDEFG
1figure it out a simple example of what MAKEARRAY does
2=MAKEARRAY(2,3,LAMBDA(a,b,a+b))
3234
4345
5equivalent of:
6=SEQUENCE(2)+SEQUENCE(,3)
7234
8345
9
Sheet1
Cell Formulas
RangeFormula
B2,B6B2=FORMULATEXT(B3)
B3:D4B3=MAKEARRAY(2,3,LAMBDA(a,b,a+b))
B7:D8B7=SEQUENCE(2)+SEQUENCE(,3)
Dynamic array formulas.
 

schardt679

Board Regular
Joined
Mar 27, 2021
Messages
56
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
It seems you would have to get the row and column count in the function itself. Then pass those into MAKEARRAY
 

Forum statistics

Threads
1,141,714
Messages
5,708,015
Members
421,540
Latest member
quocbinh

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