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

Well-known Member
Joined
Mar 8, 2021
Messages
860
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
Another example with SCAN that I forgot to mention, inspired by Mike's ExcelIsFun YT : Excel Statistical Analysis 09: Location, Mean, Median, Mode, Moving Average, Skew & More!
(min 33:00)
It's about calculating moving average.
ABYROW.xlsx
ABCDEFGHIJKLMNOPQR
1lambda "formula", Moving Average, using SCAN
2
3What if you need the "moving average", like average of last 7 days or last 12 months?
4Moving averages "smooth out" numbers to give you a better over view of the trend.(all numeric data, same format)
5single cell (input array, entire column, formula knows where to stop)
6Month EndSales6-month Moving Ave.=LET(a,C7:C37,nm,6,s,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,AVERAGE(INDEX(a,SEQUENCE(nm,,i))))),FILTER(s,NOT(ISERR(s))))
731-07-22614,212494,631=AVERAGE(C7:C12)494,631
830-06-22551,250456,528=AVERAGE(C8:C13)456,528
931-05-22352,220410,297=AVERAGE(C9:C14)410,297
1030-04-22607,028405,090=AVERAGE(C10:C15)405,090
1131-03-22494,401342,149=AVERAGE(C11:C16)342,149
1228-02-22348,672283,648=AVERAGE(C12:C17)283,648
1331-01-22385,596273,681=AVERAGE(C13:C18)273,681
1431-12-21273,864279,941=AVERAGE(C14:C19)279,941
1530-11-21320,980282,871=AVERAGE(C15:C20)282,871
1631-10-21229,383328,734=AVERAGE(C16:C21)328,734
1730-09-21143,395337,529=AVERAGE(C17:C22)337,529
1831-08-21288,865394,946=AVERAGE(C18:C23)394,946
1931-07-21423,159393,964=AVERAGE(C19:C24)393,964
2030-06-21291,445385,404=AVERAGE(C20:C25)385,404
2131-05-21596,158366,708=AVERAGE(C21:C26)366,708
2230-04-21282,149314,443=AVERAGE(C22:C27)314,443
2331-03-21487,897358,810=AVERAGE(C23:C28)358,810
2428-02-21282,977315,192=AVERAGE(C24:C29)315,192
2531-01-21371,800307,968=AVERAGE(C25:C30)307,968
2631-12-20179,265289,454=AVERAGE(C26:C31)289,454
2730-11-20282,570282,478=AVERAGE(C27:C32)282,478
2831-10-20548,348268,041=AVERAGE(C28:C33)268,041
2930-09-20226,192219,677=AVERAGE(C29:C34)219,677
3031-08-20239,634260,147=AVERAGE(C30:C35)260,147
3131-07-20260,717295,960=AVERAGE(C31:C36)295,960
3230-06-20137,409278,240=AVERAGE(C32:C37)278,240
3331-05-20195,947drag and drop formula knows
3430-04-20258,165has to stop herewhere to stop
3531-03-20469,009
3629-02-20454,513
3731-01-20154,396
38
moving average
Cell Formulas
RangeFormula
F6F6=FORMULATEXT(H7)
D7:D32D7=AVERAGE(C7:C12)
E7:E32E7=FORMULATEXT(D7)
H7:H32H7=LET(a,C7:C37,nm,6,s,SCAN(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,AVERAGE(INDEX(a,SEQUENCE(nm,,i))))),FILTER(s,NOT(ISERR(s))))
Dynamic array formulas.
 
ABYROW.xlsx
ABCDEFGHIJKLMNOPQRS
1Moving Average , different approach, unpivoting, using BYROW and ABYROW
2
3=LET(a,C5:C35,nm,6,r,ROWS(a)-nm+1,s,SEQUENCE(,nm)+SEQUENCE(r)-1,x,INDEX(a,s),BYROW(x,LAMBDA(a,AVERAGE(a))))
4Month EndSales=LET(a,C5:C35,nm,6,ABYROW(SEQUENCE(ROWS(a)-nm+1),LAMBDA(i,AVERAGE(INDEX(a,SEQUENCE(,6)+i-1)))))
531-07-22614,212494,631494,631
630-06-22551,250456,528456,528To prove that ABYROW is "natively" capable (BYROW is not)
731-05-22352,220410,297410,297to "spread" (spill) a horizontal pattern from a vertical array (single values/row).
830-04-22607,028405,090405,090ABYROW is can embed a pattern created from a column.
931-03-22494,401342,149342,149=ABYROW(SEQUENCE(26),LAMBDA(i,SEQUENCE(,6)+i-1))
1028-02-22348,672283,648283,648123456
1131-01-22385,596273,681273,681234567
1231-12-21273,864279,941279,941345678
1330-11-21320,980282,871282,871456789
1431-10-21229,383328,734328,7345678910
1530-09-21143,395337,529337,52967891011
1631-08-21288,865394,946394,946789101112
1731-07-21423,159393,964393,9648910111213
1830-06-21291,445385,404385,40491011121314
1931-05-21596,158366,708366,708101112131415
2030-04-21282,149314,443314,443111213141516
2131-03-21487,897358,810358,810121314151617
2228-02-21282,977315,192315,192131415161718
2331-01-21371,800307,968307,968141516171819
2431-12-20179,265289,454289,454151617181920
2530-11-20282,570282,478282,478161718192021
2631-10-20548,348268,041268,041171819202122
2730-09-20226,192219,677219,677181920212223
2831-08-20239,634260,147260,147192021222324
2931-07-20260,717295,960295,960202122232425
3030-06-20137,409278,240278,240212223242526
3131-05-20195,947222324252627
3230-04-20258,165232425262728
3331-03-20469,009242526272829
3429-02-20454,513252627282930
3531-01-20154,396262728293031
36
mov avrg 2
Cell Formulas
RangeFormula
E3E3=FORMULATEXT(E5)
G4,I9G4=FORMULATEXT(G5)
E5:E30E5=LET(a,C5:C35,nm,6,r,ROWS(a)-nm+1,s,SEQUENCE(,nm)+SEQUENCE(r)-1,x,INDEX(a,s),BYROW(x,LAMBDA(a,AVERAGE(a))))
G5:G30G5=LET(a,C5:C35,nm,6,ABYROW(SEQUENCE(ROWS(a)-nm+1),LAMBDA(i,AVERAGE(INDEX(a,SEQUENCE(,6)+i-1)))))
I10:N35I10=ABYROW(SEQUENCE(26),LAMBDA(i,SEQUENCE(,6)+i-1))
Dynamic array formulas.
 
Tweaked ABYROW lambda helper function to display a message if no results are returned, first variable after LET, "nr", easy to change if we want so.
Chose as default nr=NA(). Considered that is more helpful if ABYROW is called by other function inner calculations and NA() is easier to handle by IFNA(….
Excel Formula:
=LAMBDA(ar,y,
    LET(nr,NA(),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,"")),IF(SEQUENCE(i)=i,z,v)))),b,IFNA(r,""),
     IFS(AND(b=""),nr,AND(COLUMNS(b)=2,INDEX(b,,2)=""),INDEX(b,,1),TRUE,b)
    )
)
ABYROW.xlsx
ABCDEFGHIJKLMNOP
1ABYROW. Handling "no result" byrow scenarios.
2"nr" variable=NA()if nr="" function returns "" , so can be confusing,
3=ABYROW(C4:D5,LAMBDA(a,FILTER(a,ISNUMBER(a))))better to have something
4ab#N/A
5cd
6
71.filter only numeric values byrow
8
9manually (drag and drop)
10F6:F12 =FILTER(A11:D11,ISNUMBER(A11:D11))=ABYROW(A11:D17,LAMBDA(a,FILTER(a,ISNUMBER(a))))
1112ab1212
12cd3e33
13fghi#CALC!
14j456456456
15789x789789
16klmn#CALC!
17o10p1110111011
18
192. count the rows that have no numeric values3. remove empty rows
20
21=SUM(--(J11:J17=""))=FILTER(J11#,J11:J17<>"")
22212
233
244. average of numbers, if empty numeric rows are considered 0's 456
25789
26=IF(J11:J17="",0,J11#)1011
2712
283
29000
30456
31789
32000
331011
34
35=AVERAGE(F27#)
363.8824
37
38compared to
39
40=AVERAGE(A11:D17)
416
42
ABYROW peek 4
Cell Formulas
RangeFormula
F3,F40,F35,F26,J21,F21,J10F3=FORMULATEXT(F4)
F4F4=ABYROW(C4:D5,LAMBDA(a,FILTER(a,ISNUMBER(a))))
F10F10="F6:F12"&" "&FORMULATEXT(F11)
J11:L17J11=ABYROW(A11:D17,LAMBDA(a,FILTER(a,ISNUMBER(a))))
F11:G11,F17:G17,F14:H15,F12:F13,F16F11=FILTER(A11:D11,ISNUMBER(A11:D11))
F22F22=SUM(--(J11:J17=""))
J22:L26J22=FILTER(J11#,J11:J17<>"")
F27:H33F27=IF(J11:J17="",0,J11#)
F36F36=AVERAGE(F27#)
F41F41=AVERAGE(A11:D17)
Dynamic array formulas.
 
Glimpse of ABYCOL, AROW.
As i said before, a separate thread will follow with all the concepts, designs, explanations, formulas unfolding, examples. Formulas are short and simple.
ABYROW is designed to "append" or "accumulate the horizontally spilled results, row by row.
ABYCOL is designed to "append" or "accumulate the vertically spilled results, column by column.
Lesson learned from recursive functions, because of its limitations and calculation times: find ways and solutions to do it in both directions, horiz. and vert., and, depending on the case scenario, always use the shorter dimension.
(ARRANGEMENTS thread covers this concept, 100's of thousands of rows and several columns)
Therefore, for scenarios that have lot more rows than columns, AROW is a function that spills the results horizontally but appends them not row by row, appends them column by column.
ABYCOL(ar,y)
Excel Formula:
=LAMBDA(ar,y,
    LET(nr,NA(),a,IF(ar="","",ar),
      r,REDUCE(0,SEQUENCE(COLUMNS(a)),
         LAMBDA(v,i,LET(w,INDEX(a,,i),f,FILTER(w,w<>""),x,IFERROR(y(f),""),j,ROWS(x)-1,z,IF(j,x,IF({1;0},x,"")),IFNA(IF(SEQUENCE(,i)=i,z,v),"")))),b,IFNA(r,""),
   IFS(AND(r=""),nr,AND(ROWS(b)=2,INDEX(b,2,)=""),INDEX(b,1,),TRUE,b)
    )
)
AROW(ar,y) calls ABYCOL
Excel Formula:
=LAMBDA(ar,y,
   LET(a,IF(ar="","",ar),r,ROWS(a),c,COLUMNS(a),sr,SEQUENCE(r),sc,SEQUENCE(,c),
      x,ABYCOL(sc,LAMBDA(i,INDEX(BYROW(a,LAMBDA(x,INDEX(y(x),i))),sr))),
      FILTER(x,NOT(BYCOL(x,LAMBDA(b,AND(b="")))))
   )
)
Let`s put them to a test.
Setup that anybody can reproduce with ease. Setup time should not take more than 10min, testing time though…..some time.
Step1. Define all the functions
Step 2. Build sample random array
Step 3. "spin value(E4) = "y"
Step 4. Set tot nr. of rows value (E3)
Step 5. "spin" = "n"
Step 6. call one of the functions, note down calc. time, place a space bar in front of " ="
Step 7. go to step 3. (delete the leading space to activate the calculation for new nr. rows value), note the results.

ABYROW.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1arraySet up array, randomly created Function to be testedFunctions to test:
2elementsfixed 10 clms wideLAMBDA(a,UNIQUE(a,1))
3nr. rows200000(unique "byrows")1. =LET( thunks, BYROW(C6#, LAMBDA(row_, LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_)))) )), rows_,ROWS(thunks), cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))), MAKEARRAY(rows_,cols_, LAMBDA(i,j, INDEX( INDEX(thunks,i,1)(), 1, j ) ) ) )
4Aspinntboulden's
5B=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))thunks
6C7C432EXSCL
7DK1Q9AKOPQW
8EON23HXWZSU
9FJCM3C93OVE
10GURKFMGW443
11HBE4T7HD85M
12II457J44217
13J33S3W06N26
14K2QVL1YD4D9
15LV5GCTL1721
16MRUJ3GM7A98
17N010BJ3SBGW
18O62GY9Q2NTF
19P15ME12ERTW
20Q366PHR642T
21RZE3964KB8B
22SSIZVC4965B2. =ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))
23T4UYTSH373P3. =AROW(C6#,LAMBDA(a,UNIQUE(a,1)))calls ABYCOL
24U8XMH917L8E4. =AFUSBYROW(C6#:L15,,1)calls AFLAT
25VZ32GH8J13C(4. classic, no lambda helper functions used)
26WXH1NWSJ2QA
27XGU6V1RZD5LNote: 2,3 and 4 functions, perform lot more intestine calculations
28Y8J0QFUGC46since are designed as "functional" functions.
29Z74JYTS8R9J(can handle , errors, null strings, wrong arguments)
300811OGYTPD3
31163O9FOH9UJ
3222E2RHZCS14links to other functions:
333T0868NJLLV(use latest posts/variants on respective threads)
3442F6EEYO29XABYROWthis thread
355K5TPS1JHQ0ABYCOLthis thread
3667W827JGX40AROWthis thread
377I89BXP53FWAFUSBYROW
388IS9KQRES3KAFLAT
39900GX166OF9
4009B18J8YAKN
411T9MPK3BEZD
4224CJX157AYIResultsthunksABYROWAROWAFUSBYROW
433689CC85VK2array rows1. calc time2. calc time3. calc time4. calc time
444JH4WGJ301D1,00010.03 s1.12 sinstantinstant
45518VJ0UQEVP2,00041.35 s5.43 sinstantinstant
466WASRNQZ9Z53,00094.44 s12.31 sinstantinstant
477S20XK4A2VX4,000192.29 s23.88 sinstantinstant
488P6W0GVJXO45,000298.22 s37.28 s0.23 sinstant
4993SXXW2CLJQ10,000??1.35 sinstant
503WIZ28H2PO100,000??12.27 s4.6 s
51I10NPUIBE3200,000??22.37 s !!!error *
52073A4D3ZFItiming errors aprox. +0.5s*using AFLAT, 200,000 rows * 10 clms=
53DJE6B16A6D!!! Quite a lot, but so far,= 2M rows (excedes 1.5M rows spreadsheet real estate
545GIQ13V280I do not have a better solution, yet!(horiz/vert size do not affect calc. time, only r*c value)
554XFFPF8E8V
563580B82103Important note:
57J91O1N6RO6If anybody has other functions, other designs, other results, please feel free to share it with us here.
583EKU4IBC42
59KEOGT2WFGGGlimpse of results returned by AROWcheck200000=ROWS(O61#)
609N4WPCC0MK=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
612S4G887XN27C432EXSL
62AAW63P5IZ0K1Q9AOPW
6327NV9F27I9ON23HXWZSU
642F8HDN3QEQJCM39OVE
6560FEU0YSD0URKFMGW43
660S7YEZPHGEBE4T7HD85M
67BBT0325HP5I457J21
688PV741YOVU3SW06N2
694MLDIU13882QVL1YD49
70I6AYY4FACQV5GCTL172
71AFQMK8TC3FRUJ3GM7A98
72WXW76N2N8L01BJ3SGW
73R55YN4370762GY9QNTF
74N1FAVI1H0J15ME2RTW
7500T58XKJX436PHR42T
764FWT129963ZE3964KB8
7791OP8QZ080SIZVC4965B
7861201GGSR64UYTSH37P
7942ZSS7NKLY8XMH917LE
ABYCOL peek 1
Cell Formulas
RangeFormula
C5,O60C5=FORMULATEXT(C6)
C6:L200005C6=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))
T59T59=ROWS(O61#)
U59U59=FORMULATEXT(T59)
O61:X200060O61=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
Dynamic array formulas.
 
New AROW , does not call any custom-made function. 18-20% faster than previous AROW ( AROW(old))
Added calculation time for sample array of 300,000 rows.
Excel Formula:
=LAMBDA(ar,y,
    LET(a,IF(ar="","",ar),
      r,REDUCE(0,SEQUENCE(,COLUMNS(a)),LAMBDA(v,i,IF(SEQUENCE(,i)=i,
        BYROW(a,LAMBDA(x,INDEX(y(FILTER(x,x<>"")),,i))),v))),b,IFERROR(r,""),
        FILTER(b,BYCOL(b,LAMBDA(x,NOT(AND(x="")))))
    )
)
ABYROW.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1arraySet up array, randomly created Function to be testedFunctions to test:
2elementsfixed 10 clms wideLAMBDA(a,UNIQUE(a,1))
3nr. rows300000(unique "byrows")1. =LET( thunks, BYROW(C6#, LAMBDA(row_, LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_)))) )), rows_,ROWS(thunks), cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))), MAKEARRAY(rows_,cols_, LAMBDA(i,j, INDEX( INDEX(thunks,i,1)(), 1, j ) ) ) )
4Aspinntboulden's
5B=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))thunks
6C4H7E7HE8DN
7DK8Z5W8N39Y
8E0464D5QB7Z
9F0JJD08QKA4
10G7K36B4N9N9
11HQA66ONJRC7
12I420LKPL0FC
13J5BU9A42054
14K5XP6S1QJQZ
15LSOP42M10UO
16M24T1CHG7N4
17NDY8BP2E827
18OC55CPTG5FY
19PUQL73XA6K2
20QEZ87FU8O4Z
21R8DAVL0K382
22S4X0PYJAU2T2. =ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))
23TF99RW560MD3. =AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (old one)calls ABYCOL
24UP5XZ203SH54. =AFUSBYROW(C6#:L15,,1)calls AFLAT
25VH002EQP71J5. =AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (new one)
26W19FM7J90Z2(4. classic, no lambda helper functions used)
27XL97D3UR8I6
28Y0LG5K5R937Note: 2,3 and 4 functions, perform lot more intestine calculations
29ZL4PKYMDW49since are designed as "functional" functions.
3008U1KB33WH7(can handle , errors, null strings, wrong arguments)
31105NSHLKY3F
322GJEJ4GH52L
333NK8WS6Q03Rlinks to other functions:
344MAOHV1U7D8(use latest posts/variants on respective threads)
355G1RZ74N1C8ABYROWthis thread
366G7105WUU2BABYCOLthis thread
3779314FX6QT1AROW (old,new))this thread
3884VPCDVU7D2AFUSBYROW
39901D6NBE76FAFLAT
400B504BKY000
411C19X0W9SZ8
422LV10C5CG5NNEW !!
433FTQ0039736ResultsthunksABYROWAROW(old)AROW(new)AFUSBYROW
4440V89A3D1U6array rows1. calc time2. calc time3. calc time5. calc time4. calc time
455GY163O0C9R1,00010.03 s1.12 sinstantinstantinstant
466084ADS1F602,00041.35 s5.43 sinstantinstantinstant
477N78T9G5Y913,00094.44 s12.31 sinstantinstantinstant
488RSCDVWCOZO4,000192.29 s23.88 sinstantinstantinstant
4992IKPM6MRE65,000298.22 s37.28 s0.23 sinstantinstant
5042324I414410,000??1.35 s1.13 sinstant
51OJW5HEVY5P100,000??12.27 s10.31 s4.6 s
520D15LE95Y7200,000??22.37 s18.91 !!!error *
537E9U910C70300,000??34.64 s29.11 !!!error *
54L55JIIOBZ9timing errors aprox. +0.5s*using AFLAT, 200,000 rows * 10 clms=
559HBSEULCA6!!! Quite a lot, but so far,= 2M rows (excedes 1.5M rows spreadsheet real estate
5638I1A8HIG2I do not have a better solution, yet !(horiz/vert size do not affect calc. time, only r*c value)
57G6T749YFSW
588NXZF7K9UVImportant note:
59MO2O8NAP5FIf anybody has other functions, other designs, other results, please feel free to share it with us here.
603IWHVG9589
61HYX3X45TCLGlimpse of results returned by AROWcheck300000=ROWS(O63#)
62BMKR7D5YP6=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
638J8PEJ7G754H7E8DN
64OEWC350A36K8Z5WN39Y
65QSY2ZJ15CX046D5QB7Z
6662NX7Q4U5W0JD8QKA4
673HVZEHHA9P7K36B4N9
683B51P967PUQA6ONJRC7
69H8P0401859420LKPFC
70973FC6RH325BU9A420
71W5EHO0ZH035XP6S1QJZ
72K885CM8A49SOP42M10U
7383MB4LD3L524T1CHG7N
74M5087D102SDY8BP2E7
758CQ31A5OQ6C5PTGFY
760D9O1O1VVGUQL73XA6K2
77GO75J22M20EZ87FUO4
78I6KER0VM2R8DAVL0K32
79EYC5K978E24X0PYJAU2T
808WYZZ802G8F9RW560MD
812N057EJE3DP5XZ203SH
82B4877W8QLGH02EQP71J
8331F4LXZYJ819FM7J0Z2
84B7N5PIVH40L97D3UR8I6
ABYCOL peek 1
Cell Formulas
RangeFormula
C5,O62C5=FORMULATEXT(C6)
C6:L300005C6=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))
T61T61=ROWS(O63#)
U61U61=FORMULATEXT(T61)
O63:X300062O63=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
Dynamic array formulas.
 
1,000,000 (1 milion) rows tested, AROW took 209.95 seconds. During this time thunks method can calculate 4,000 something rows.
ABYROW.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1arraySet up array, randomly created Function to be testedFunctions to test:
2elementsfixed 10 clms wideLAMBDA(a,UNIQUE(a,1))
3nr. rows1000000(unique "byrows")1. =LET( thunks, BYROW(C6#, LAMBDA(row_, LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_)))) )), rows_,ROWS(thunks), cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))), MAKEARRAY(rows_,cols_, LAMBDA(i,j, INDEX( INDEX(thunks,i,1)(), 1, j ) ) ) )
4Aspinytboulden's
5B=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))thunks
6CKHPZ85OPJN
7D64GY5K9CJT
8E0RF9P0IA16
9FD1MCC83MSL
10G8LG6RE034X
11HS7F8788YC8
12INQ51NW328X
13J25P88R9157
14K49UM4K0IFR
15LGBH6Z624MB
16MV1JFEQ2QUG
17NSI4369FWR0
18OJU98A3744B
19P4610UIVH45
20QZC6B1D25GV
21RQ41IB9I67T
22SD10FKNV8OG2. =ABYROW(C6#,LAMBDA(a,UNIQUE(a,1)))
23TCHBECHLM2D3. =AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (old one)calls ABYCOL
24U2LWIFTFNYJ4. =AFUSBYROW(C6#:L15,,1)calls AFLAT
25VOX0RTZRK0O5. =AROW(C6#,LAMBDA(a,UNIQUE(a,1))) (new one)
26W5L7B4NO679(4. classic, no lambda helper functions used)
27XU05EY990C1
28Y1HCNHICI78Note: 2,3 and 4 functions, perform lot more intestine calculations
29Z935KI5NZI8since are designed as "functional" functions.
300M8WW0P8O6C(can handle , errors, null strings, wrong arguments)
3113CFN470609
32215TS76TWM5
333LX02V4D0R5links to other functions:
344Y079SLAQEE(use latest posts/variants on respective threads)
355CCPS19XR5IABYROWthis thread
366ITL9XTAQ7PABYCOLthis thread
377BCF4GGM48RAROW (old,new))this thread
388I1X3WLDHRBAFUSBYROW
3992T3R9UQS9JAFLAT
40070285GMCX2
411WEDLA9FP55
4224BIHG2DU78NEW !!
433R7F4BPXE1PResultsthunksABYROWAROW(old)AROW(new)AFUSBYROW
444720SSHGEBQarray rows1. calc time2. calc time3. calc time5. calc time4. calc time
455H4FFYI89W41,00010.03 s1.12 sinstantinstantinstant
46696Q525MUJ42,00041.35 s5.43 sinstantinstantinstant
4770382U6Y8QT3,00094.44 s12.31 sinstantinstantinstant
488MC0X9Z0X1P4,000192.29 s23.88 sinstantinstantinstant
499G69D0RVWRD5,000298.22 s37.28 s0.23 sinstantinstant
507UI938G50710,000??1.35 s1.13 sinstant
51C2SG5Q7K60100,000??12.27 s10.31 s4.6 s
52O498T75U4Y200,000??22.37 s18.91 !!!error *
53P5T0435RC0300,000??34.64 s29.11 !!!error *
543B8T1JMNC4timing errors aprox. +0.5s*using AFLAT, 200,000 rows * 10 clms=
55V8W1BBF49T!!! Quite a lot, but so far,= 2M rows (excedes 1.5M rows spreadsheet real estate
565CI62LZI7FI do not have a better solution, yet !(horiz/vert size do not affect calc. time, only r*c value)
578PQEQ2256B
58N4QR3D9CPJImportant note:
5961O8J9JHYJIf anybody has other functions, other designs, other results, please feel free to share it with us here.
606VQ08HG1EO
6112C9115IM0Glimpse of results returned by AROWcheck1000000=ROWS(O63#)
62Q30OZ6U78Z=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
632IFTHFTXB6KHPZ85OJN
64BGEFBXJCCU64GY5K9CJT
650NQ15M304W0RF9PIA16
66D3N6WDA64JD1MC83SL
67L89SPR111B8LG6RE034X
688ZIMQ8J4OES7F8YC
697904TPD7W4NQ51W328X
707TDJSM5F8O25P8R917
71860QQ0GC7949UMK0IFR
72SZ4014J9XIGBH6Z24M
73HT82HW0317V1JFEQ2UG
749ZS1ER6N7CSI4369FWR0
7547EB7Z1SL2JU98A374B
7667MVYHVJAI4610UIVH5
77YH7U2518O4ZC6B1D25GV
78RY7049H02CQ41IB967T
79E0ZOPKBF57D10FKNV8OG
8078Z1OHKC2FCHBELM2D
812MF4F3IGYX2LWIFTNYJ
825U53GTEB58OX0RTZK
83VU3D2V47BW5L7B4NO69
84K903QGR09WU05EY9C1
85KW41PN1M131HCNI78
ABYCOL peek 1
Cell Formulas
RangeFormula
C5,O62C5=FORMULATEXT(C6)
C6:L1000005C6=IF(E4="n",C6#,LET(a,INDEX(A4:A49,RANDARRAY(E3,10,1,46,1)),IFERROR(--a,a)))
T61T61=ROWS(O63#)
U61U61=FORMULATEXT(T61)
O63:X1000062O63=AROW(C6#,LAMBDA(a,UNIQUE(a,1)))
Dynamic array formulas.
 
Another example, inspired from Mike's today's YT (21-Jan-22) Excel Statistical Analysis 15: 5-Number Summary / Box & Whiskers Chart: Power Query & Array Formulas
ACOL is easy to deduct since it is "perpendicular" to AROW.
Excel Formula:
=LAMBDA(ar,y,LET(a,IF(ar="","",ar),
     r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(SEQUENCE(i)=i,
       BYCOL(a,LAMBDA(x,INDEX(y(FILTER(x,x<>"")),i,))),v))),b,IFERROR(r,""),
          FILTER(b,BYROW(b,LAMBDA(x,NOT(AND(x="")))))
    )
)
ABYROW.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1cpaTblformula to unpivot table
2CPA Test 1CPA Test 2CPA Test 3CPA Test 4CPA Test 5=LET(a,cpaTbl,r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(r*c),q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,IF({1,0},INDEX(cpaTbl[#Headers],m),INDEX(a,q,m)))
32228433555CPA Test 122
44914324553CPA Test 2281. if input data table format (cpaTbl)
5705526130CPA Test 343Note: Sample table has less rows than original, to fit real-estate of mini-sheet
6136553768CPA Test 435lambda argument quartile.inc(a,sequence(5)-1) spills vertically
75280515258CPA Test 555
84467625531CPA Test 149lambda formula
91881564561CPA Test 214=REDUCE(0,SEQUENCE(5),LAMBDA(v,i,IF(SEQUENCE(i)=i,BYCOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,i-1))),v)))
10524731757CPA Test 3321355413
114951474487CPA Test 4454449343753
123767125760CPA Test 5535158494459
137866645866CPA Test 1707066625366
147581615066CPA Test 257881896187
157063495364CPA Test 352
165115673932CPA Test 461=ACOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,SEQUENCE(5)-1)))
175054343462CPA Test 5301355413
187050896017CPA Test 1134449343753
197154404013CPA Test 2655158494459
20724973459CPA Test 357066625366
211762474377CPA Test 4377881896187
224558694356CPA Test 568
236264313674CPA Test 1522. if input data as 2 column array
24CPA Test 280=LET(a,H3:H107,b,I3:I107,c,COUNTA(UNIQUE(a)),r,ROWS(a)/c,q,INDEX(b,SEQUENCE(r,c)),ACOL(q,LAMBDA(x,QUARTILE.INC(x,SEQUENCE(5)-1))))
25CPA Test 3511355413
26CPA Test 4524449343753
27CPA Test 5585158494459
28CPA Test 1447066625366
29CPA Test 2677881896187
30CPA Test 362
31CPA Test 455
32CPA Test 531
33CPA Test 118
34CPA Test 281
35CPA Test 356
36CPA Test 445
37CPA Test 561
38CPA Test 152
39CPA Test 247
40CPA Test 331
41CPA Test 47
42CPA Test 557
43CPA Test 149
44CPA Test 251
45CPA Test 347
46CPA Test 444
47CPA Test 587
48CPA Test 137
49CPA Test 267
50CPA Test 312
51CPA Test 457
52CPA Test 560
53CPA Test 178
54CPA Test 266
55CPA Test 364
56CPA Test 458
57CPA Test 566
58CPA Test 175
59CPA Test 281
60CPA Test 361
61CPA Test 450
62CPA Test 566
63CPA Test 170
64CPA Test 263
65CPA Test 349
66CPA Test 453
67CPA Test 564
68CPA Test 151
69CPA Test 215
70CPA Test 367
71CPA Test 439
72CPA Test 532
73CPA Test 150
74CPA Test 254
75CPA Test 334
76CPA Test 434
77CPA Test 562
78CPA Test 170
79CPA Test 250
80CPA Test 389
81CPA Test 460
82CPA Test 517
83CPA Test 171
84CPA Test 254
85CPA Test 340
86CPA Test 440
87CPA Test 513
88CPA Test 172
89CPA Test 249
90CPA Test 373
91CPA Test 44
92CPA Test 559
93CPA Test 117
94CPA Test 262
95CPA Test 347
96CPA Test 443
97CPA Test 577
98CPA Test 145
99CPA Test 258
100CPA Test 369
101CPA Test 443
102CPA Test 556
103CPA Test 162
104CPA Test 264
105CPA Test 331
106CPA Test 436
107CPA Test 574
108
ACOL
Cell Formulas
RangeFormula
H2,K24,K16,K9H2=FORMULATEXT(H3)
H3:I107H3=LET(a,cpaTbl,r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(r*c),q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,IF({1,0},INDEX(cpaTbl[#Headers],m),INDEX(a,q,m)))
K10:O14K10=REDUCE(0,SEQUENCE(5),LAMBDA(v,i,IF(SEQUENCE(i)=i,BYCOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,i-1))),v)))
K17:O21K17=ACOL(cpaTbl,LAMBDA(a,QUARTILE.INC(a,SEQUENCE(5)-1)))
K25:O29K25=LET(a,H3:H107,b,I3:I107,c,COUNTA(UNIQUE(a)),r,ROWS(a)/c,q,INDEX(b,SEQUENCE(r,c)),ACOL(q,LAMBDA(x,QUARTILE.INC(x,SEQUENCE(5)-1))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ExternalData_1=ACOL!$B$2:$F$23H3
 
ABYROW.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1both orientations, when data 2clms array
2CPA Test 122
3CPA Test 228=UNIQUE(A2:A106)
4CPA Test 343↓↓=LET(a,A2:A106,b,B2:B106,r,ROWS(UNIQUE(a)),AROW(INDEX(b,SEQUENCE(r)+(SEQUENCE(,ROWS(a)/r)-1)*r),LAMBDA(x,QUARTILE.INC(x,SEQUENCE(,5)-1))))
5CPA Test 435CPA Test 11344517078
6CPA Test 555CPA Test 2549586681
7CPA Test 149CPA Test 3534496289
8CPA Test 214CPA Test 4437445361
9CPA Test 332CPA Test 51353596687
10CPA Test 445
11CPA Test 553=TRANSPOSE(UNIQUE(A2:A106))
12CPA Test 170CPA Test 1CPA Test 2CPA Test 3CPA Test 4CPA Test 5
13CPA Test 251355413
14CPA Test 3524449343753
15CPA Test 4615158494459
16CPA Test 5307066625366
17CPA Test 1137881896187
18CPA Test 265E13=LET(a,A2:A106,b,B2:B106,c,ROWS(UNIQUE(a)),ACOL(INDEX(b,SEQUENCE(,c)+(SEQUENCE(ROWS(a)/c)-1)*c),LAMBDA(x,QUARTILE.INC(x,SEQUENCE(5)-1))))
19CPA Test 35
20CPA Test 437
21CPA Test 568
22CPA Test 152
23CPA Test 280
24CPA Test 351
25CPA Test 452
26CPA Test 558
27CPA Test 144
28CPA Test 267
29CPA Test 362
30CPA Test 455
31CPA Test 531
32CPA Test 118
33CPA Test 281
34CPA Test 356
35CPA Test 445
36CPA Test 561
37CPA Test 152
38CPA Test 247
39CPA Test 331
40CPA Test 47
41CPA Test 557
42CPA Test 149
43CPA Test 251
44CPA Test 347
45CPA Test 444
46CPA Test 587
47CPA Test 137
48CPA Test 267
49CPA Test 312
50CPA Test 457
51CPA Test 560
52CPA Test 178
53CPA Test 266
54CPA Test 364
55CPA Test 458
56CPA Test 566
57CPA Test 175
58CPA Test 281
59CPA Test 361
60CPA Test 450
61CPA Test 566
62CPA Test 170
63CPA Test 263
64CPA Test 349
65CPA Test 453
66CPA Test 564
67CPA Test 151
68CPA Test 215
69CPA Test 367
70CPA Test 439
71CPA Test 532
72CPA Test 150
73CPA Test 254
74CPA Test 334
75CPA Test 434
76CPA Test 562
77CPA Test 170
78CPA Test 250
79CPA Test 389
80CPA Test 460
81CPA Test 517
82CPA Test 171
83CPA Test 254
84CPA Test 340
85CPA Test 440
86CPA Test 513
87CPA Test 172
88CPA Test 249
89CPA Test 373
90CPA Test 44
91CPA Test 559
92CPA Test 117
93CPA Test 262
94CPA Test 347
95CPA Test 443
96CPA Test 577
97CPA Test 145
98CPA Test 258
99CPA Test 369
100CPA Test 443
101CPA Test 556
102CPA Test 162
103CPA Test 264
104CPA Test 331
105CPA Test 436
106CPA Test 574
107
ACOL 2
Cell Formulas
RangeFormula
D3D3=FORMULATEXT(D5)
E4,E11E4=FORMULATEXT(E5)
D5:D9D5=UNIQUE(A2:A106)
E5:I9E5=LET(a,A2:A106,b,B2:B106,r,ROWS(UNIQUE(a)),AROW(INDEX(b,SEQUENCE(r)+(SEQUENCE(,ROWS(a)/r)-1)*r),LAMBDA(x,QUARTILE.INC(x,SEQUENCE(,5)-1))))
E12:I12E12=TRANSPOSE(UNIQUE(A2:A106))
E13:I17E13=LET(a,A2:A106,b,B2:B106,c,ROWS(UNIQUE(a)),ACOL(INDEX(b,SEQUENCE(,c)+(SEQUENCE(ROWS(a)/c)-1)*c),LAMBDA(x,QUARTILE.INC(x,SEQUENCE(5)-1))))
E18E18=FORMULATEXT(E13)
Dynamic array formulas.
 
As you all know already, we have a set of 14 amazing new functions. Excel Team really means business.
Having more and more built-in function tools, very specific for very specific simple tasks, each with a lot of arguments for fine tuning, strengthens the idea I had here, a while ago: excel future will be more about lambda "formulas" and not about custom made lambda functions. I think that more than 90% of common excel tasks spectrum covered by all Excel content creators, can be solved now with simple short lambda formulas. And this thread is living proof of the idea.
In this context, if we still want to design competitive cm lambdas (custom-made), they should push the limits and address concepts that Excel cannot cover, not because they can't, because they must play safe.
That's why we do not have, yet, lambda helper functions that can spill byrow or bycol. It's safer to deliver a single result.
This should not discourage us from writing cm lambda functions, quite the opposite, but, will not be too much about the function itself anymore.
It will be more about the concept design, its simplicity, versatility, and efficiency.
It will be more about the techniques used and studies of the spectrum of tasks it can solve with as many examples as possible.
Why ? Because a cm function will always need to be updated with every new release of new built-in functions (or even replaced entirely), while the concept and techniques will stay.
Mastering the designs and the techniques will build up our confidence and skills to solve complicated tasks with simple short formulas that hold the concept idea.
A cm function is only a volatile framework holder of its concept.
However, there is a fundamental difference between cm functions and built-in ones, apart from the obvious reasons, and I am positive that Excel Team is aware of that.
A cm function does not look good if it calls too many other cm functions or if has too many arguments. But if it calls for example 10 "native" ones it's ok, it's "legit", no complaints.
It's like translating a word using only words that also need translation. Defeats the purpose of communication.
What the Excel Team is offering us is a well written comprehensive dictionary and we should be grateful for that.
More words in the dictionary, more sentences or even phrases can be developed, and everybody will understand them.
I have so many functions that I did not "publish" because I did not want them to be "lost in translation" and now they can be shared because they can be translated into "native" Excel.
My functions will no longer need to call AFLAT, APP2H, APP2V, ARESIZE and AFILL, ASTACK, AUNSTACK, ASELECT and ARRANGE, they will call TOCOL, HSTACK, VSTACK, DROP and TAKE and EXPAND, WRAPROWS, WRAPCOLS, CHOOSECOLS and CHOOSEROWS.
Needless to say, the versatility of built-in functions, which can support multiple arguments like array1,array2,…. is priceless and more difficult for us to reproduce, though, not imposible.
In this respect I dare to wish for arrays of arguments/variables with an index tool to be able to access them iteratively, like in second syntax of INDEX where we can write an array of ranges INDEX((range1, range2…),row_num,col_num,area_num) and call them one by one using area_num index tool and count them with AREAS function.
But not only with ranges that reside in same spreadsheet, should be possible for any arguments or variables.
Even if it was pure coincidence, I wished before for more recursion iterations and it happened. ✌️?
To conclude, fantastic job Excel Team, Excel future looks brighter and brighter, can't wait to see what is still to come, can't wait to see them widely implemented for all users.
This niche one-of-a-kind forum will soon become a very crowded place.
 
14 new functions but still no lambda helper function that can spill byrow/bycol.
I have addressed this functionality before in all the ways possible since day one on this forum, 1 year ago.
Before recursion, in an "analogical" way, functions like AFILTER, AUNQSRT, AUNIQUE and later AFUSBYROW, still "analog".
After recursion, with a third party "accumulator" that appends or stacks row by row the results recursively, like AAGREGATE that uses APPEND2V, or without a stacking accumulator, using native IF stacking or appending, like the functions designed using the DIY Array Recursive Function Kit techniques, ARF.
After lambda helper functions, AGGR and here presented drafts of ABYROW , AROW.
Now we can use a built-in "legit" "accumulator" for staking, VSTACK function.
These are the latest refined drafts of custom-made lambda helper spill capable byrow functions, 2 dif approaches, w or w/o VSTACK, more versatile than ever before.
Looking forward to find the right amount of spare time to present and explain them in a separate thread. Till then, they belong here.
They have the same functionality, and both share the same arguments:
ar: array
fn: function lambda helper argument LAMBDA(x,fn(x))
[er]: error message argument, if the function delivers no results

ABYROW(ar,fn,[er])
- does not call any of the new functions, uses native IF functionality to stack the rows, single REDUCE function construction and some mandatory cosmetics tricks. (with BYCOL)
Excel Formula:
=LAMBDA(ar,fn,[er],
    LET(
        a, IF(ar = "", "", ar),
        r, REDUCE(
            0,
            SEQUENCE(ROWS(a)),
            LAMBDA(v,i,
                LET(
                    x, INDEX(a, i, ),
                    y, IF(
                        COLUMNS(x) = 1,
                        fn(INDEX(a, i, 1)),
                        fn(FILTER(x, x <> ""))
                    ),
                    c, COLUMNS(y),
                    z, IF(c = 1, INDEX(y, {1,2}), y),
                    IF(SEQUENCE(i) = i, z, v)
                )
            )
        ),
        b, IFERROR(r, ""),
        d, BYCOL(b, LAMBDA(x, AND(x = ""))),
        FILTER(b, NOT(d), IF(ISOMITTED(er), NA(), er))
    )
)

SPILLBYROWS(ar,fn,[er])
- calls new, now "native" VSTACK, same structure single REDUCE, not so many cosmetics needed, (used other of the new ones, DROP), therefore even shorter.
Excel Formula:
=LAMBDA(ar,fn,[er],
    LET(
        e, IF(ISOMITTED(er), NA(), er),
        a, IF(ar = "", "", ar),
        r, REDUCE(
            0,
            SEQUENCE(ROWS(a)),
            LAMBDA(v,i,
                LET(
                    x, INDEX(a, i, ),
                    y, IFERROR(
                        IF(
                            COLUMNS(x) = 1,
                            fn(INDEX(a, i, 1)),
                            fn(FILTER(x, x <> ""))
                        ),
                        ""
                    ),
                    VSTACK(v, y)
                )
            )
        ),
        d, DROP(IFNA(r, ""), 1),
        IF(AND(d = ""), e, d)
    )
)

Notes: Finally, with the help of Advanced Formula Environment add in, I can present them in a decent way.
Until Excel comes with a bult-in lambda helper that will do the same I will use them (probably the 2nd one) extensively on any construction possible because they can help a great deal.
 

Forum statistics

Threads
1,223,880
Messages
6,175,152
Members
452,615
Latest member
bogeys2birdies

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