ASCAN

ASCAN(a,[d])
a
array
[d]
direction argument ; 0 or omitted scan by array, -1 by rows, 1 by clms

Array SCAN, 3 in 1 function, SCAN by row, by column, by array

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
ASCAN, Array SCAN, 3 in 1 function, SCAN by row, by column, by array. This is my take of tboulden's SCANBYROW/BYCOL.
Uses only new!! SCAN lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.
Excel Formula:
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMNOPQRST
1d,omitted (by array)d,-1 (by rows)d,1 (by clms)
2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)
3123413610136101234
45678152128365111826681012
5910111245556678919304215182124
613141516911051201361327425828323640
7
8d,omitted (by array)d,-1 (by rows)d,1 (by clms)
9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)
10123413610136101234
115678152128365111826681012
12910111245556678919304215182124
1313141516911051201361327425828323640
14171819201531711902101735547445505560
15212223242312532763002143669066727884
16
17Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post
Cell Formulas
RangeFormula
F2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)
F3:I6F3=ASCAN(A3:D6)
K3:N6K3=ASCAN(A3:D6,-1)
P3:S6P3=ASCAN(A3:D6,1)
A10:D15A10=SEQUENCE(6,4)
F10:I15F10=ASCAN(A10#)
K10:N15K10=ASCAN(A10#,-1)
P10:S15P10=ASCAN(A10#,1)
Dynamic array formulas.
 
Upvote 0

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
leila SCAN.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
1Existing alternative functions : We already have functions shorter than a formula bar row that can handle these tasks.
2
3MonthSalesSCAN by monthsingle cell year SCAN
4Jan-191=ARESIZE(Sales[Sales],,12)=ARESIZE(E11#,,1)=ARESIZE(ASCAN(ARESIZE(Sales[Sales],,12),1),,1)
5Feb-1921.12345678910111213.1
6Mar-19312345678910111222
7Apr-19412345678910111233
8May-19512345678910111244
9Jun-19655
10Jul-197=ASCAN(E5#,1)66
11Aug-1982.12345678910111277
12Sep-1992468101214161820222488
13Oct-191036912151821242730333699
14Nov-191148121620242832364044481010
15Dec-19121111
16Jan-201SCAN by year1212
17Feb-2021.2.3.22single cell month SCAN
18Mar-203=AUNSTACK(Sales[Sales],12)=ASCAN(E19#,1)=ASTACK(J19#,1)44=ASTACK(ASCAN(AUNSTACK(Sales[Sales],12),1),1)
19Apr-204111111111661
20May-205222233333883
21Jun-20633336666610106
22Jul-20744441010101010121210
23Aug-20855551515151515141415
24Sep-20966662121212121161621
25Oct-201077772828282828181828
26Nov-201188883636363636202036
27Dec-201299994545454545222245
28Jan-211101010105555555555242455
29Feb-2121111111166666666663366
30Mar-2131212121278787878786678
31Apr-2141991
32May-215312123
33Jun-216other functions615156
34Jul-217ASTACK10181810
35Aug-218AUNSTACK15212115
36Sep-219ARESIZE21242421
37Oct-211028272728
38Nov-211136303036
39Dec-211245333345
40Jan-22155363655
41Feb-222664466
42Mar-223788878
43Apr-224112121
44May-225316163
45Jun-226620206
46Jul-22710242410
47Aug-22815282815
48Sep-22921323221
49Oct-221028363628
50Nov-221136404036
51Dec-221245444445
5255484855
536666
547878
5511
5633
5766
581010
591515
602121
612828
623636
634545
645555
656666
667878
67
Sheet3
Cell Formulas
RangeFormula
E4,W18,J18,E10,U4E4=FORMULATEXT(E5)
Q4,D18,N18Q4=FORMULATEXT(R5)
E5:P8E5=ARESIZE(Sales[Sales],,12)
R5:R52R5=ARESIZE(E11#,,1)
U5:U52U5=ARESIZE(ASCAN(ARESIZE(Sales[Sales],,12),1),,1)
E11:P14E11=ASCAN(E5#,1)
E19:H30E19=AUNSTACK(Sales[Sales],12)
J19:M30J19=ASCAN(E19#,1)
O19:O66O19=ASTACK(J19#,1)
W19:W66W19=ASTACK(ASCAN(AUNSTACK(Sales[Sales],12),1),1)
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
Getting closer to the last examples of SCAN/REDUCE, lambda "formulas" that deserve to be shared.
Did this first time ages ago, with recursion. AAGGREGATE and other functions uses same concept.
The concept is quite simple, if we want to apply a function to an array (calculations, sorting, filtering, extractions...etc), row by row, a function that spills (horizontally), we apply it row by row, recursively, and we append the results, row by row, for all the rows of initial array, one by one.
At that time as an appending "accumulator" I have used APPEND2V (also recursive). There is even a newer version APP2V, non-recursive, but both can deal with 2D arrays and have more features that are not needed in this case.
We need a simple function that appends vertically an array "a" to a single row "b", will call it A1V(a,b).
Now, having this new "accumulator", let's see if we can replace the recursion with REDUCE.
A1V(a,b)
Excel Formula:
=LAMBDA(a,b,LET(r,ROWS(a)+1,s,SEQUENCE(r),IF(s=r,b,a)))
LAMBDA 1.2.1.xlsx
ABCDEFGHIJKLMNOPQRSTUV
11. A1V(a,b)
2rws a+rws b=2+1=3
3a=IF(SEQUENCE(3)=3,A7:B7,A4:B5)=A1V(A4:B5,A7:B7)
4ababab
5cdcdcd
6befef
7ef
8
92. lambda formula to replace recursion with REDUCE
10If y(a) is the function to apply to every row
11=reduce("",seq(rws(a)),lambda(v,i,A1V(v,y(index(a,i,)))))
12-initial value v="",i: iteration nr., accumulator: A1V(v,y(index(a,i,))
13
14lamda "formula" to sort in ascending order each row ( sort(a,,,1) 1 argument for sorting by clm)
15a=REDUCE("",SEQUENCE(3),LAMBDA(v,i,A1V(v,SORT(INDEX(A16:C18,i,),,,1))))
16acb =A1V("",A7:B7)
17123abcthe first row is blank because initial value is "" and first iteration is A1V=("",sort(first row of a)), like in  
18321123ef
19123
20
21to amend the formula to "cut" first row
22=LET(a,A16:C18,r,ROWS(a),s,SEQUENCE(r),x,REDUCE("",s,LAMBDA(v,i,A1V(v,SORT(INDEX(a,i,),,,1)))),INDEX(x,s+1,SEQUENCE(,COLUMNS(x))))
23abc
24123
25123
26
27sorting in descending order each row
28=LET(a,A16:C18,r,ROWS(a),s,SEQUENCE(r),x,REDUCE("",s,LAMBDA(v,i,A1V(v,SORT(INDEX(a,i,),,-1,1)))),INDEX(x,s+1,SEQUENCE(,COLUMNS(x))))
29cba
30321
31321Instead of SORT we can write any function y=y(a)=LAMBDA(a,FUNCTION(a)) ( has to spill horizontally)
32
ASCAN np1
Cell Formulas
RangeFormula
D3,J3,E28,E22,S16,E15D3=FORMULATEXT(D4)
D4:E6D4=IF(SEQUENCE(3)=3,A7:B7,A4:B5)
J4:K6J4=A1V(A4:B5,A7:B7)
E16:G19E16=REDUCE("",SEQUENCE(3),LAMBDA(v,i,A1V(v,SORT(INDEX(A16:C18,i,),,,1))))
S17:T18S17=A1V("",A7:B7)
E23:G25E23=LET(a,A16:C18,r,ROWS(a),s,SEQUENCE(r),x,REDUCE("",s,LAMBDA(v,i,A1V(v,SORT(INDEX(a,i,),,,1)))),INDEX(x,s+1,SEQUENCE(,COLUMNS(x))))
E29:G31E29=LET(a,A16:C18,r,ROWS(a),s,SEQUENCE(r),x,REDUCE("",s,LAMBDA(v,i,A1V(v,SORT(INDEX(a,i,),,-1,1)))),INDEX(x,s+1,SEQUENCE(,COLUMNS(x))))
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
Following the concept of previous post, here is how a function looks like, Introducing AROW(a,y) function.
a: array
y: lambda "container" of y(a) LAMBDA(a,y(a))

Note 1: This is a preliminary design, y(a), only one argument (variable).
We can use functions with more variables y(a,k,...)
Note 2: Not a huge fan of "lambda" arguments. When we use it for data that lives in a range, firsthand calculations, is ok. I like to design functions that can live in other functions, interchanging variables, for intuitive useful repetitive tasks. If we master well these lambda helper functions, for firsthand calculations, a simple lambda "formula" can do the job in most of the cases. If arguments lengths exceed the length of some average custom functions, we will start to write functions to write arguments. 😉
AROW(a,y)
Excel Formula:
=LAMBDA(a,y,LET(r,ROWS(a),
     x,REDUCE("",SEQUENCE(r),LAMBDA(v,i,A1V(v,y(INDEX(IF(a="","",a),i,))))),
     IFERROR(INDEX(x,SEQUENCE(r,,2),SEQUENCE(,COLUMNS(x))),"")
    )
)
LAMBDA 1.2.1.xlsx
ABCDEFGHIJKLMNOPQRSTU
1asorting ascendingsorting descending
22113y,LAMBDA(a,SORT(a,,,-1))y,LAMBDA(a,SORT(a,,,-1))
33220=AROW(A2:D7,LAMBDA(a,SORT(a,,,-1)))=AROW(A2:D7,LAMBDA(a,SORT(a,,-1,-1)))
4123211233211
5321002233220
6213412233221
7222101233210
812344321
912222221
10
11unique valuesunique values ascending order
12y,LAMBDA(a,UNIQUE(a,1))y,LAMBDA(a,SORT(UNIQUE(a,1),,,-1))
13=AROW(A2:D7,LAMBDA(a,UNIQUE(a,1)))=AROW(A2:D7,LAMBDA(a,SORT(UNIQUE(a,1),,,-1)))
14213123
15320023
16123123
1732100123
1821341234
192112
20
21exclude blanks, align leftfilter text,excluding blanks
22y,LAMBDA(a,FILTER(a,a<>""))y,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>"")))
23a=AROW(A24:E26,LAMBDA(a,FILTER(a,a<>"")))=AROW(A24:E26,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>""))))
24ab12ab12ab
251e3cd1e3cdecd
263cd43cd4cd
27
28filter numbers
29y,LAMBDA(a,FILTER(a,ISNUMBER(a)))
30=AROW(A24:E26,LAMBDA(a,FILTER(a,ISNUMBER(a))))
3112
3213
3334
34
ASCAN np 2
Cell Formulas
RangeFormula
F3,F23F3=FORMULATEXT(G4)
M3,G30,M23,M13M3=FORMULATEXT(M4)
G4:J9G4=AROW(A2:D7,LAMBDA(a,SORT(a,,,-1)))
M4:P9M4=AROW(A2:D7,LAMBDA(a,SORT(a,,-1,-1)))
E13E13=FORMULATEXT(G14)
G14:J19G14=AROW(A2:D7,LAMBDA(a,UNIQUE(a,1)))
M14:P19M14=AROW(A2:D7,LAMBDA(a,SORT(UNIQUE(a,1),,,-1)))
G24:K26G24=AROW(A24:E26,LAMBDA(a,FILTER(a,a<>"")))
M24:O26M24=AROW(A24:E26,LAMBDA(a,FILTER(a,ISTEXT(a)*(a<>""))))
G31:H33G31=AROW(A24:E26,LAMBDA(a,FILTER(a,ISNUMBER(a))))
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
Another example using REDUCE, inspired from Leila's today's YT (13-Jan-22): Easily Combine Text & Numbers into ONE Cell in Excel | NO CONCATENATE.
Task: Lambda "formula" to sum array formatted with TEXT
Other functions on minisheet: T_CHARS , AKEEP
Book1
ABCDEFGHIJKLMNO
1NameAmount BaseBonusTask: Lambda "formula" to sum array formatted with TEXTother functions
2Bill100050T_CHARS
3Nick2500601st method, when we have a pattern (digits after ":")AKEEP
4Sara3600100single cell for whole row(last posts functions)
5Jessica10020=SUM(--REPLACE(B6:C6,1,SEARCH(":",B6:C6),""))
6Base: 7,200Bonus: 23007430
7
8=SUM(B6:C6)2nd method, works for any text format (REDUCE method)
9="Bonus: "&TEXT(SUM(C2:C5),"#,##0")single cell for whole row
10="Base: "&TEXT(SUM(B2:B5),"#,##0")=LET(a,B6:C6,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
117430
12
13=LET(a,B14:D14,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
14Base: $ 7,200Bonus# 1,230$Other/$2,740$/11170
15
16
17Base: $ 7,200Other/$2,740$/=LET(a,B17:C18,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
18Bonus# 1,230$$200$HGFgh11370
19
203rd method, dedicated functions, any text format
21=SUM(AKEEP(B6:C6,T_CHARS(1)))
22Big advantage of using dedicated function,=SUM(AKEEP(B17:C18,T_CHARS(1)))=SUM(AKEEP(B14:D14,T_CHARS(1)))
23can handle decimal points with ease74301137011170
24because of T_CHARS versatility
25=AKEEP(B26:C27,T_CHARS(1,,"."))=SUM(AKEEP(B26:C27,T_CHARS(1,,".")))
26Base: $ 7,200.01Other/$2,740.03$/7200.012740.0311370.1
27Bonus# 1,230.02$$200.04$HGFgh1230.02200.04
28
29=SUM(F26#)
3011370.1
31
Sheet1
Cell Formulas
RangeFormula
F5,F29,F25,J25,H22,L22,F17,F13,F10F5=FORMULATEXT(F6)
B6B6="Base: "&TEXT(SUM(B2:B5),"#,##0")
C6C6="Bonus: "&TEXT(SUM(C2:C5),"#,##0")
D6D6=SUM(B6:C6)
F6F6=SUM(--REPLACE(B6:C6,1,SEARCH(":",B6:C6),""))
D8D8=FORMULATEXT(D6)
C9C9=FORMULATEXT(C6)
B10B10=FORMULATEXT(B6)
F11F11=LET(a,B6:C6,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
F14F14=LET(a,B14:D14,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
F18F18=LET(a,B17:C18,REDUCE(0,a,LAMBDA(v,x,v+CONCAT(IFERROR(--MID(x,SEQUENCE(LEN(x)),1),"")))))
F21F21=FORMULATEXT(F23)
F23F23=SUM(AKEEP(B6:C6,T_CHARS(1)))
H23H23=SUM(AKEEP(B17:C18,T_CHARS(1)))
L23L23=SUM(AKEEP(B14:D14,T_CHARS(1)))
F26:G27F26=AKEEP(B26:C27,T_CHARS(1,,"."))
J26J26=SUM(AKEEP(B26:C27,T_CHARS(1,,".")))
F30F30=SUM(F26#)
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No time to rest. Another example for REDUCE, this time, Mike's ExcelIsFun today's YT (13-jan-22) : FILTER, LET, TRANSPOSE and Other Excel Functions: Mode Calculation for each Quartile. EMT 1771
We managed before to make an array to spill results horizontally, "byrow" style, with the help of another function, called "accumulator". (A1V function)
There is a way to do it without any external "accumulator". An extensive study will be published separately my first custom-made lambda helper function AROW or ABYROW, a lambda helper function that spills, and does not call any other function.
ETM1771-Ch03-ESA.xlsm
ABCDEFGHIJKLMNOPQRSTU
1Formula D11 : =LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1), REDUCE(0,SEQUENCE(4),LAMBDA(v,i, LET(x,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IFNA(IF(SEQUENCE(i)=i,y,v),"")))))
2Data=QUARTILE.INC(B3:B36,D4:D8)
32QuartValueMode/s
4402
51012012=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E4)*($B$3:$B$36<E5))))
61224131=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E5)*($B$3:$B$36<E6))))
712367.544=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E6)*($B$3:$B$36<E7))))
8134796870=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E7)*($B$3:$B$36<E8))))
915
1018single cell formula, spills horizontally
111912
122331
132444
14306870
1531
1631first try was a shorter formula that had this problem:
1731=LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,IF(SEQUENCE(i)=i,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),v))))
18381212
19393131why is that? REDUCE appends with every iteration another row.
20434444If the final result will "spill" max n clms, if one row spills a single value, will spread this single result to all clms.
21446870
2244Like in this example:to append a single value "7" at the bottom of the array "a"
2352a=IF(SEQUENCE(3)=3,7,H24:J25)
2457123123
2561456456
2664777
2766
2868to overcome this behavior(that happens only for single values) we have to "force" "7" in a 2 elem array ({7,""})
2968
3070=IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25)=IFNA(IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25),"")
3170123123
3271456456
33727#N/A7
3473
3577This explain this part of final formula:
3679...y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IFNA(IF(SEQUENCE(i)=i,y,v),"")
37
38
ModeInQuartile1
Cell Formulas
RangeFormula
E2E2=FORMULATEXT(E4)
E4:E8E4=QUARTILE.INC(B3:B36,D4:D8)
F5:F7,F8:G8F5=TRANSPOSE(MODE.MULT(FILTER($B$3:$B$36,($B$3:$B$36>=E4)*($B$3:$B$36<E5))))
H5:H8H5=FORMULATEXT(F5)
D11:E14D11=LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,LET(x,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),y,IF(COLUMNS(x)=1,IF({1,0},x,""),x),IFNA(IF(SEQUENCE(i)=i,y,v),"")))))
D17,H30,L30,L23D17=FORMULATEXT(D18)
D18:E21D18=LET(a,B3:B36,q,QUARTILE.INC(a,SEQUENCE(5)-1),x,XMATCH(a,q,-1),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,IF(SEQUENCE(i)=i,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),v))))
L24:N26L24=IF(SEQUENCE(3)=3,7,H24:J25)
H31:J33H31=IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25)
L31:N33L31=IFNA(IF(SEQUENCE(3)=3,IF({1,0},7,""),H24:J25),"")
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
AROW example ETM1771-Ch03-ESA.xlsm
ABCDEFGHIJKLMNOPQRSTU
1We had to amend the previous formula, to cover scenarios when 4'th quartile range max value is also one of MODE.MULT values (2 occurences)
2(to simulate this scenario we modified last 2 values of "Data" array, 4'th quartile, to be max ones and both=79)
3
4=QUARTILE.INC(A6:A39,SEQUENCE(5)-1)As we see, last xmatch values are 5, and they should "stay" 4 (5 values can have only 4 inner intervals/quartiles)
5Data=XMATCH(A6:A39,C6#,-1)=IF(E6#=5,4,E6#)
62211
742011Amended formula:
8104111 =LET(a,A6:A39,xm,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(xm=5,4,xm), REDUCE(0,SEQUENCE(4),LAMBDA(v,i, LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))
91267.511
10127911
11131112
12151131
13181144
141911687079
152322
162422
173022
183122
193122
203122
213822
223922
234333
244433
254433
265233
275733
286133
296433
306633
316844
326844
337044
347044
357144
367244
377344
387954
397954
40
Sheet1
Cell Formulas
RangeFormula
C4C4=FORMULATEXT(C6)
E5,H5E5=FORMULATEXT(E6)
C6:C10C6=QUARTILE.INC(A6:A39,SEQUENCE(5)-1)
E6:E39E6=XMATCH(A6:A39,C6#,-1)
H6:H39H6=IF(E6#=5,4,E6#)
J11:L14J11=LET(a,A6:A39,xm,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(xm=5,4,xm),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Modified more values through "Data" array to check if formula spills correctly. (1st and 3rd quartile)
C4 formula:
Excel Formula:
=LET(a,A4:A37,xm,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(xm=5,4,xm),
    REDUCE(0,SEQUENCE(4),LAMBDA(v,i,
       LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))
AROW example ETM1771-Ch03-ESA.xlsm
ABCDEFGH
1Modified "Data" to check if formula spills correctly.
2
3Data
4241219
5431
644466
712687079
812
913
1015
1119
1219
1323
1424
1530
1631
1731
1831
1938
2039
2143
2244
2344
2452
2557
2661
2766
2866
2968
3068
3170
3270
3371
3472
3573
3679
3779
38
Sheet2
Cell Formulas
RangeFormula
C4:E7C4=LET(a,A4:A37,xm,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(xm=5,4,xm),REDUCE(0,SEQUENCE(4),LAMBDA(v,i,LET(m,TRANSPOSE(MODE.MULT(FILTER(a,x=i))),r,IF(COLUMNS(m)=1,IF({1,0},m,""),m),IFNA(IF(SEQUENCE(i)=i,r,v),"")))))
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
This thread, which covered so many examples using SCAN; REDUCE, deserves the first peek to (my) first custom made lambda helper function based on REDUCE, ABYROW , before opening a separate thread for it.
ABYROW is a BYROW lambda helper function that can spills results, horizontally, has same syntax:
=ABYROW(array,LAMBDA(row))
ABYROW(ar,y)
ar: array
y: lambda argument

Excel Formula:
=LAMBDA(ar,y,
    LET(a,IF(ar="","",ar),r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,
       LET(w,INDEX(a,i,),f,FILTER(w,w<>""),x,IFERROR(y(f),""),j,COLUMNS(x)-1,z,IF(j,x,IF({1,0},x,"")),IFNA(IF(SEQUENCE(i)=i,z,v),"")))),
         IF(AND(COLUMNS(r)=2,INDEX(r,,2)=""),INDEX(r,,1),r)
    )
)
ABYROW.xlsx
ABCDEFGHIJKLMNOPQR
1Using ABYROW to solve previous task. Checking that ABYROW spills horizontally.
2
3Data
42=LET(a,A4:A37,m,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(m=5,4,m),ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(MODE.MULT(FILTER(a,x=i))))))
5441219
6431
7124466
812687079
913
1015
1119
1219
1323
1424
1530
1631
1731
1831
1938
2039
2143
2244
2344
2452
2557
2661
2766
2866
2968
3068
3170
3270
3371
3472
3573
3679
3779
38
ABYROW peek 1
Cell Formulas
RangeFormula
C4C4=FORMULATEXT(C5)
C5:E8C5=LET(a,A4:A37,m,XMATCH(a,QUARTILE.INC(a,SEQUENCE(5)-1),-1),x,IF(m=5,4,m),ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(MODE.MULT(FILTER(a,x=i))))))
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
ABYROW.xlsx
ABCDEFGHIJKLMNOPQR
1Task: Extract the quartile sections of "Data" as separate rows. Unpivoting a vertical array in uneven rows, based on row pattern only.
2
3=QUARTILE.INC(A5:A38,SEQUENCE(5)-1)
4Data=LET(x,XMATCH(A5:A38,C5#,-1),IF(x=5,4,x))
5221
64201=ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(FILTER(A5:A38,E5#=i))))
74411244121213151919
81267.512324303131313839
9127914344445257616666
10131686870707172737979
11151
12191How easy is now to get MODE.MULT with "spilling" ABYROW
13191
14232=ABYROW(G7#,LAMBDA(a,TRANSPOSE(MODE.MULT(a))))
1524241219
1630231
173124466
18312687079
19312
20382
21392
22433
23443
24443
25523
26573
27613
28663
29663
30684
31684
32704
33704
34714
35724
36734
37794
38794
39
ABYROW peek 2
Cell Formulas
RangeFormula
C3C3=FORMULATEXT(C5)
E4,G14,G6E4=FORMULATEXT(E5)
C5:C9C5=QUARTILE.INC(A5:A38,SEQUENCE(5)-1)
E5:E38E5=LET(x,XMATCH(A5:A38,C5#,-1),IF(x=5,4,x))
G7:O10G7=ABYROW(SEQUENCE(4),LAMBDA(i,TRANSPOSE(FILTER(A5:A38,E5#=i))))
G15:I18G15=ABYROW(G7#,LAMBDA(a,TRANSPOSE(MODE.MULT(a))))
Dynamic array formulas.
 

Xlambda

Active Member
Joined
Mar 8, 2021
Messages
361
Office Version
  1. 365
Platform
  1. Windows
ABYROW.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Task. Unflatten an array for a certain pattern.
2Unflatten a vertical array into 3 rows. 1st row has 6 elements, 2nd 4, and 3rd 5 elements.
3
4step1.step2. row pattern
5elements/row=SCAN(0,C6:C8,LAMBDA(v,a,v+a))=XMATCH(SEQUENCE(MAX(E6#)),E6#,1)
6a661
7b4101step 3. ABYROW
8c5151=ABYROW(SEQUENCE(ROWS(C6:C8)),LAMBDA(i,TRANSPOSE(FILTER(A6:A20,I6#=i))))
9d1abcdef
10eor1ABCD
11f112345
12A=ASCAN(C6:C8)2
13B62
14C102
15D152
1613
1723
1833
1943
2053
21
ABYROW peek 3
Cell Formulas
RangeFormula
E5,I5,E12,K8E5=FORMULATEXT(E6)
E6:E8E6=SCAN(0,C6:C8,LAMBDA(v,a,v+a))
I6:I20I6=XMATCH(SEQUENCE(MAX(E6#)),E6#,1)
K9:P11K9=ABYROW(SEQUENCE(ROWS(C6:C8)),LAMBDA(i,TRANSPOSE(FILTER(A6:A20,I6#=i))))
E13:E15E13=ASCAN(C6:C8)
Dynamic array formulas.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,470
Messages
5,764,513
Members
425,221
Latest member
MercedesCLK

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