AGGR

[Xlambda]

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.

=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.

 

[Xlambda]

Note to moderators: Very first row of the post, the one reserved for array syntax does not allow "[kn]", optional argument syntax between brackets.
AGGR(a,fn,[kn]) should be read . Who would have thought that Excel will introduce this ?!! ?✌

 

[Xlambda]

Note to moderators: Very first row of the post, the one reserved for array syntax does not allow "[kn]", optional argument syntax between brackets.
AGGR(a,fn,[kn]) should be read . Who would have thought that Excel will introduce this ?!! ?✌

Solved !! by smozgur in 0.1 sec . ?✌

 

[smozgur]

Actually, it took a couple of hours as I didn't see your message in this thread.
Thanks for catching this quickly.