Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 837
- Office Version
- 365
- Platform
- Windows
AGGR Aggregate By Rows 22 Excel functions, can handle array arguments. !! NEW !! functions ISOMITTED , BYROW , SCAN , MAKEARRAY used
Similar to AAGGREGATE but does not need recursion.
Calls A1GGR(a,fn,[k])=LAMBDA(a,fn,[k],BYROW(a,LAMBDA(a,IFS(fn<14,SWITCH(fn,1,AVERAGE(a),2,COUNT(a),3,SUM(--(a<>"")),4,MAX(a),5,MIN(a),6,PRODUCT(a),7,STDEV.S(a),8,STDEV.P(a),9,SUM(a),10,VAR.S(a),11,VAR.P(a),12,MEDIAN(a),13,MODE.SNGL(a)),fn<=19,AGGREGATE(fn,6,a,k),fn=20,AND(a),fn=21,OR(a),fn=22,XOR(a),TRUE,"check arg"))))
A1GGR function could have been integrated in the main one, but I have kept it separate because is much easier to add excel or custom made functions anytime we want.
We simply add the function inside A1GGR at the very end of formula , just before "TRUE" (...,fn=23,CUSTOM(a,k),TRUE,"check arg"...)and change "nf" variable inside AGGR with total count of fn, "nf" variable, first variable after LET, that's all.
Similar to AAGGREGATE but does not need recursion.
Calls A1GGR(a,fn,[k])=LAMBDA(a,fn,[k],BYROW(a,LAMBDA(a,IFS(fn<14,SWITCH(fn,1,AVERAGE(a),2,COUNT(a),3,SUM(--(a<>"")),4,MAX(a),5,MIN(a),6,PRODUCT(a),7,STDEV.S(a),8,STDEV.P(a),9,SUM(a),10,VAR.S(a),11,VAR.P(a),12,MEDIAN(a),13,MODE.SNGL(a)),fn<=19,AGGREGATE(fn,6,a,k),fn=20,AND(a),fn=21,OR(a),fn=22,XOR(a),TRUE,"check arg"))))
A1GGR function could have been integrated in the main one, but I have kept it separate because is much easier to add excel or custom made functions anytime we want.
We simply add the function inside A1GGR at the very end of formula , just before "TRUE" (...,fn=23,CUSTOM(a,k),TRUE,"check arg"...)and change "nf" variable inside AGGR with total count of fn, "nf" variable, first variable after LET, that's all.
Excel Formula:
=LAMBDA(a,fn,[kn],
LET(nf,22,cd,ISOMITTED(kn),k,IF(cd,0,kn),rw,ROWS(a),cl,COLUMNS(fn),ck,COLUMNS(k),s,SCAN(0,fn,LAMBDA(v,a,v+(a>13)*(a<20))),
m,MAX(s),IFS(OR(ISNA(XMATCH(fn,SEQUENCE(,nf)))),"check fn",AND(NOT(cd),m<>ck),"check kn",TRUE,LET(kk,INDEX(k,s),
MAKEARRAY(rw,cl,LAMBDA(r,c,LET(x,INDEX(fn,c),y,INDEX(kk,c),z,TRANSPOSE(A1GGR(a,x,y)),IFNA(INDEX(z,r),""))))))
)
)| LAMBDA 1.1.1.xlsx | ||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
| 1 | 22 functions | NEW!! Functions used BYROW, SCAN, MAKEARRAY, ISOMITTED | ||||||||||||||||||||||||||
| 2 | fn argument | A1GGR: Array 1 (single 1D results array, because of BYROW functionality) that can Aggregate 22 functions, by each row (single value arguments) | ||||||||||||||||||||||||||
| 3 | ↓ | function | Note 1: This function could have been integrated in the main one, but I have kept it separate because is much easier to add excel or custom made functions anytime we want. | |||||||||||||||||||||||||
| 4 | 1 | Average | We simply add the function inside A1GGR at the end of SWITCH formula, and change "nf" variable inside AGGR with total count of fn, "nf" variable, first variable after LET, that's all. | |||||||||||||||||||||||||
| 5 | 2 | Count | Note 2: Did not use AGGREGATE function for fn [1,13] because of its range syntax. They can not handle array arguments. fn [13,19] do handle array arguments. | |||||||||||||||||||||||||
| 6 | 3 | Counta | Similar to AAGGREGATE but no recursion | |||||||||||||||||||||||||
| 7 | 4 | Max | fn,1,kn,omitted | fn,9,kn,omitted | fn,14,kn,2 | |||||||||||||||||||||||
| 8 | 5 | Min | sample | =A1GGR(D9:M14,1) | =A1GGR(D9:M14,9) | =A1GGR(D9:M14,14,2) | ||||||||||||||||||||||
| 9 | 6 | Product | 1 | 3 | a | 3 | 6 | b | 2 | 3 | 10 | 4 | 28 | 6 | ||||||||||||||
| 10 | 7 | Stdev.S | 3 | d | 7 | 6 | f | -4 | 11 | 9 | 5.333 | 32 | 9 | |||||||||||||||
| 11 | 8 | Stdev.P | 5 | c | -4 | c | -3 | d | -3 | 0 | 8 | 0.5 | 3 | 5 | ||||||||||||||
| 12 | 9 | Sum | 9 | -5 | 4 | 1 | 12 | r | t | w | 7 | 4.667 | 28 | 9 | ||||||||||||||
| 13 | 10 | Var.S | 11 | 3 | 5 | x | x | 5 | 8 | 6 | 6.333 | 38 | 8 | |||||||||||||||
| 14 | 11 | Var.P | s | -2 | 53 | 0 | 4 | 0 | 5 | 10 | 60 | 5 | ||||||||||||||||
| 15 | 12 | Median | ||||||||||||||||||||||||||
| 16 | 13 | Mode.Sngl | meaning of kn | AGGR: Aggregate Array by Rows, can handle array arguments, 22 functions | ||||||||||||||||||||||||
| 17 | 14 | Large | Return the kn'th largest value | |||||||||||||||||||||||||
| 18 | 15 | Small | Return the kn'th smallest value | fn,sequence(,13),kn,omitted | ||||||||||||||||||||||||
| 19 | 16 | Percentile.Inc | Return the kn'th percentile | 0<=kn<=1 | =AGGR(D9:M14,SEQUENCE(,13)) | |||||||||||||||||||||||
| 20 | 17 | Quartile.Inc | Return the kn'th quartile | 0<=kn<=4(<5) | 4 | 7 | 9 | 10 | 1 | 3240 | 3.055 | 2.828 | 28 | 9.333 | 8 | 3 | 3 | |||||||||||
| 21 | 18 | Percentile.Exc | Return the kn'th percentile | 0<kn<1 | 5.333 | 6 | 8 | 11 | -4 | -49896 | 5.317 | 4.853 | 32 | 28.27 | 23.56 | 6.5 | ||||||||||||
| 22 | 19 | Quartile.Exc | Return the kn'th quartile | 1<=kn<=3(<4) | 0.5 | 6 | 9 | 8 | -4 | 0 | 4.93 | 4.5 | 3 | 24.3 | 20.25 | -1.5 | -3 | |||||||||||
| 23 | 20 | AND | 4.667 | 6 | 9 | 12 | -5 | -15120 | 6.088 | 5.558 | 28 | 37.07 | 30.89 | 5.5 | ||||||||||||||
| 24 | 21 | OR | fn,kn syntax | 6.333 | 6 | 8 | 11 | 3 | 39600 | 2.805 | 2.56 | 38 | 7.867 | 6.556 | 5.5 | 5 | ||||||||||||
| 25 | 22 | XOR | nr. of kn arg.=nr.fn arg. that require kn | 10 | 6 | 7 | 53 | -2 | 0 | 21.23 | 19.38 | 60 | 450.8 | 375.7 | 2 | 0 | ||||||||||||
| 26 | Examples: | |||||||||||||||||||||||||||
| 27 | fn={1,2,15,9,14,3,18} kn={2,3,0.5} is ok | =SEQUENCE(,6,14) | ||||||||||||||||||||||||||
| 28 | if we need to extract average,3rd and 2nd largest,sum | fn | 14 | 15 | 16 | 17 | 18 | 19 | ||||||||||||||||||||
| 29 | fn={1,14,14,9} kn will be {3,2} | fn,{21,22,23},kn,omitted | kn | 2 | 2 | 0.2 | 2 | 0.2 | 2 | |||||||||||||||||||
| 30 | =AGGR(D9:M14,{1,14,14,9},{3,2}) | sample | =AGGR(J31:L33,{20,21,22}) | =AGGR(D9:M14,S28#,S29:X29) | ||||||||||||||||||||||||
| 31 | 4 | 3 | 6 | 28 | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | 6 | 2 | 2.2 | 3 | 1.6 | 3 | ||||||||||||
| 32 | 5.333 | 7 | 9 | 32 | FALSE | TRUE | TRUE | FALSE | TRUE | FALSE | 9 | 3 | 3 | 6.5 | -1.2 | 6.5 | ||||||||||||
| 33 | 0.5 | 0 | 5 | 3 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | 5 | -3 | -3 | -1.5 | -3.6 | -1.5 | ||||||||||||
| 34 | 4.667 | 7 | 9 | 28 | 9 | 1 | 1 | 5.5 | -2.6 | 5.5 | ||||||||||||||||||
| 35 | 6.333 | 6 | 8 | 38 | 8 | 5 | 5 | 5.5 | 3.8 | 5.5 | ||||||||||||||||||
| 36 | 10 | 4 | 5 | 60 | 5 | 0 | 0 | 2 | -1.2 | 2 | ||||||||||||||||||
| 37 | if N(kn)<>N(fn,13<fn<20) | complex fn,kn arguments | ||||||||||||||||||||||||||
| 38 | =AGGR(D9:M14,{2,15,15,12,14},{2,3}) | kn is 1val.short | fn,{1,10,11,14,14,15,15},kn,{2,3,2,3} | |||||||||||||||||||||||||
| 39 | check kn | =AGGR(D9:M14,{1,10,11,14,14,15,15},{2,3,2,3}) | ||||||||||||||||||||||||||
| 40 | if fn out of range | 4 | 9.333 | 8 | 6 | 3 | 2 | 3 | ||||||||||||||||||||
| 41 | =AGGR(D9:M14,{1,2,3,23}) | 5.333 | 28.27 | 23.56 | 9 | 7 | 3 | 6 | ||||||||||||||||||||
| 42 | check fn | 0.5 | 24.3 | 20.25 | 5 | 0 | -3 | -3 | ||||||||||||||||||||
| 43 | if kn out of range (kn for fn=18 ,should be<1) | 4.667 | 37.07 | 30.89 | 9 | 7 | 1 | 4 | ||||||||||||||||||||
| 44 | =AGGR(D9:M14,{16,18},{1,1}) | 6.333 | 7.867 | 6.556 | 8 | 6 | 5 | 5 | ||||||||||||||||||||
| 45 | 10 | #NUM! | 10 | 450.8 | 375.7 | 5 | 4 | 0 | 0 | |||||||||||||||||||
| 46 | 11 | #NUM! | ||||||||||||||||||||||||||
| 47 | 8 | #NUM! | ||||||||||||||||||||||||||
| 48 | 12 | #NUM! | ||||||||||||||||||||||||||
| 49 | 11 | #NUM! | ||||||||||||||||||||||||||
| 50 | 53 | #NUM! | ||||||||||||||||||||||||||
| 51 | ||||||||||||||||||||||||||||
AGGR post | ||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| O8,R8,U8,D44,D41,L39,D38,S30,N30,D30,S27,K19 | O8 | =FORMULATEXT(O9) |
| E9,G14,F13 | E9 | ="" |
| O9:O14 | O9 | =A1GGR(D9:M14,1) |
| R9:R14 | R9 | =A1GGR(D9:M14,9) |
| U9:U14 | U9 | =A1GGR(D9:M14,14,2) |
| K20:W25 | K20 | =AGGR(D9:M14,SEQUENCE(,13)) |
| S28:X28 | S28 | =SEQUENCE(,6,14) |
| D31:G36 | D31 | =AGGR(D9:M14,{1,14,14,9},{3,2}) |
| N31:P33 | N31 | =AGGR(J31:L33,{20,21,22}) |
| S31:X36 | S31 | =AGGR(D9:M14,S28#,S29:X29) |
| D39 | D39 | =AGGR(D9:M14,{2,15,15,12,14},{2,3}) |
| L40:R45 | L40 | =AGGR(D9:M14,{1,10,11,14,14,15,15},{2,3,2,3}) |
| D42 | D42 | =AGGR(D9:M14,{1,2,3,23}) |
| D45:E50 | D45 | =AGGR(D9:M14,{16,18},{1,1}) |
| Dynamic array formulas. | ||
Last edited by a moderator:
Upvote
0
