AAGGREGATE

=AAGGREGATE(a,fn,k)
a
array
fn
function number 0,min,1,max,2,and,3,or,4,xor,5,sum,6,product,7,average,8,count,9,counta,10,median,11,small,12,large
k
k argument only for fn=11(SMALL) and fn=12(LARGE) , otherwise ignored
array aggregate by each row of an array, 13 various functions
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
AAGGREGATE !! recursive !! array aggregate by each row of an array, various functions (0-MIN,1-MAX, 2-AND, 3-OR, 4-XOR, 5-SUM, 6-PRODUCT, 7-AVERAGE, 8-COUNT, 9-COUNTA, 10-MEDIAN, 11-SMALL, 12-LARGE, calls APPEND2V
Excel Formula: 
=LAMBDA(a,fn,k,
    LET(n,ROWS(a),c,COLUMNS(a),y,INDEX(a,n,),x,SWITCH(fn,0,MIN(y),1,MAX(y),2,AND(y),3,OR(y),4,XOR(y),
       5,SUM(y),6,PRODUCT(y),7,AVERAGE(y),8,COUNT(y),9,SUM(--(y<>"")),10,MEDIAN(y),11,SMALL(y,k),12,LARGE(y,k),"check data"),
       IF(n=1,APPEND2V(x,a,2),APPEND2V(AAGGREGATE(INDEX(a,SEQUENCE(n-1),SEQUENCE(,c)),fn,k),x,))
    )
)
LAMBDA 6.0.xlsx
ABCDEFGHIJKLMNOPQRSTU
1fnf-ctionfn015671011122kls
20MIN1234515151203324
31MAX67891061040302408879
42AND111213141511156536036013131214
53OR1617181920162090186048018181719
64XOR21222324252125115637560023232224
75SUM262728293026301401710072028282729
86PRODUCT313233343531351653895584033333234
97AVERAGE363738394036401907896096038383739
108COUNT4142434445414521514661108043434244
119COUNTA4647484950465024025425120048484749
1210MEDIAN5152535455515526541745132053535254
1311SMALL5657585960566029065538144058585759
1412LARGE6162636465616531599118656063636264
1566676869706670340145236168068686769
1671727374757175365207112680073737274
1776777879807680390288480192078787779
1881828384858185415393618204083838284
1986878889908690440527391216088888789
2091929394959195465695286228093939294
219697989910096100490903450240098989799
22
23fn89234
24a-1TRUEb14TRUETRUEFALSETRUETRUE
250bd13FALSETRUETRUETRUEFALSE
26432544FALSEFALSEFALSEFALSEFALSE
27
AAGGREGATE post
Cell Formulas
RangeFormula
D2:H21D2=SEQUENCE(20,5)
K2:P21K2=AAGGREGATE($D$2#,K1,)
Q2:R21Q2=AAGGREGATE($D$2#,Q1,$S$1)
K24:K26K24=AAGGREGATE($D$24:$H$26,K23,)
L24:L26L24=AAGGREGATE($D$24:$H$26,9,)
M24:O26M24=AAGGREGATE($Q$24:$R$26,M23,)
Dynamic array formulas.
 
Upvote 0
9-Feb-2022. Latest Excel update, recursion limits 16x larger !!.
AAGGREGATE was the first function that came into my mind to check the new recursion limits, a function designed with recursion "by row", long before BYROW emerged.
The "accumulator" (common argument concept now days in new lambda helper functions SCAN/REDUCE) used by AAGGREGATE is APPEND2V
Here are the results: staggering 1817 rows, 1817 recursive iterations, before, the limit was 100 something iterations.
CM Lambda helper functions.xlsx
ABCDEFGHIJKLMN
1sample rows1817sample rows1818
2fn,0 (triggers MIN)
3=SEQUENCE(C1,3)=AAGGREGATE(A4#,0,)=SEQUENCE(I1,3)=AAGGREGATE(G4#,0,)
41231123#NUM!
54564456
67897789
710111210101112row nr. check
813141513131415A4:1817=ROWS(A4#)
916171816161718E4:1817=ROWS(E4#)
1019202119192021G4:1818=ROWS(G4#)
1122232422222324
1225262725252627
1328293028282930
1431323331313233
1534353634343536
1637383937373839
1740414240404142
1843444543434445
1946474846464748
2049505149495051
2152535452525354
2255565755555657
2358596058585960
2461626361616263
2564656664646566
2667686967676869
2770717270707172
2873747573737475
2976777876767778
3079808179798081
3182838482828384
3285868785858687
3388899088888990
3491929391919293
3594959694949596
3697989997979899
37100101102100100101102
38103104105103103104105
39106107108106106107108
40109110111109109110111
41112113114112112113114
42115116117115115116117
43118119120118118119120
44121122123121121122123
45124125126124124125126
46127128129127127128129
47130131132130130131132
48133134135133133134135
49136137138136136137138
50139140141139139140141
AAGREGATE
Cell Formulas
RangeFormula
A3,K3,E3,G3A3=FORMULATEXT(A4)
A4:C1820,G4:I1821A4=SEQUENCE(C1,3)
E4:E1820,K4E4=AAGGREGATE(A4#,0,)
L8L8=ROWS(A4#)
M8:M10M8=FORMULATEXT(L8)
L9L9=ROWS(E4#)
L10L10=ROWS(G4#)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

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