Average with large and small function

[rexysri]

Hello lovely humans,

I'm trying to average the values of column-B, C, & D based on top-3 values of Column-E.
Dean wise averages i want . Here i am attaching a pic plz help me

 

[Sahak]

Hi Rexysri,

It is not completely clear to me your assignment, but I think the formulas below can give you some idea

 

[Peter_SSs]

For the future, you are likely to get much faster responses if you provide your sample data and results with XL2BB so that helpers do not have to manually type it out to test.

Since you have M/Soft 365, you could also try these.

22 04 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	HYD	34	41	57	133			HYD	34	47	46	127
3	HYD	36	54	43	132			KNL	45	53	59	157
4	HYD	32	45	39	116
5	HYD	31	40	44	115			HYD	31	45	46	122
6	HYD	24	45	46	114			KNL	44	48	57	150
7	KNL	44	61	57	161
8	KNL	49	49	61	159
9	KNL	41	50	60	151
10	KNL	46	39	55	141
11	KNL	41	42	54	138
Averages

Cell Formulas
Range	Formula
I2:L3	I2	=ROUND(AVERAGE(INDEX(FILTER(B$2:B$11,$A$2:$A$11=$H2),SEQUENCE(3))),0)
I5:L6	I5	=ROUND(AVERAGE(INDEX(FILTER(B$2:B$11,$A$2:$A$11=$H5),SEQUENCE(5))),0)

 

[rexysri]

For the future, you are likely to get much faster responses if you provide your sample data and results with XL2BB so that helpers do not have to manually type it out to test.

Since you have M/Soft 365, you could also try these.

22 04 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	HYD	34	41	57	133			HYD	34	47	46	127
3	HYD	36	54	43	132			KNL	45	53	59	157
4	HYD	32	45	39	116
5	HYD	31	40	44	115			HYD	31	45	46	122
6	HYD	24	45	46	114			KNL	44	48	57	150
7	KNL	44	61	57	161
8	KNL	49	49	61	159
9	KNL	41	50	60	151
10	KNL	46	39	55	141
11	KNL	41	42	54	138
Averages

Cell Formulas
Range	Formula
I2:L3	I2	=ROUND(AVERAGE(INDEX(FILTER(B$2:B$11,$A$2:$A$11=$H2),SEQUENCE(3))),0)
I5:L6	I5	=ROUND(AVERAGE(INDEX(FILTER(B$2:B$11,$A$2:$A$11=$H5),SEQUENCE(5))),0)

Hi peter thanks for your reply, your formula working good but here, first we have to check largest three values in column E, in example file i sorted the Column E and top-3 and 5 averages was taken. Here i want the averages without sorting the Column E.

Thanking you

 

[Peter_SSs]

in example file i sorted the Column E ..... Here i want the averages without sorting the Column E.

Important to make your sample data representative of your actual data.

We do not know anything about your data other that what you show us or tell us so if you show us sorted data, we assume that your data is sorted.

Try this instead

22 04 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	KNL	44	61	57	161			HYD	34	47	46	127
3	KNL	41	50	60	151			KNL	45	53	59	157
4	HYD	24	45	46	114
5	HYD	32	45	39	116			HYD	31	45	46	122
6	KNL	46	39	55	141			KNL	44	48	57	150
7	HYD	36	54	43	132
8	HYD	34	41	57	133
9	HYD	31	40	44	115
10	KNL	49	49	61	159
11	KNL	41	42	54	138
Averages (2)

Cell Formulas
Range	Formula
I2:L3	I2	=LET(rws,$A$2:$A$11=$H2,ROUND(AVERAGE(INDEX(SORTBY(FILTER(B$2:B$11,rws),FILTER($E$2:$E$11,rws),-1),SEQUENCE(3))),0))
I5:L6	I5	=LET(rws,$A$2:$A$11=$H5,ROUND(AVERAGE(INDEX(SORTBY(FILTER(B$2:B$11,rws),FILTER($E$2:$E$11,rws),-1),SEQUENCE(5))),0))

 

[rexysri]

Important to make your sample data representative of your actual data.

We do not know anything about your data other that what you show us or tell us so if you show us sorted data, we assume that your data is sorted.

Try this instead

It's Working Peter brother thank you soo much

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[Peter_SSs]

You may not have it yet but the new TAKE() function allows a slightly better way than INDEX/SEQUENCE to 'take' the first 3 (or 5) values to calculate with.

22 04 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	KNL	44	61	57	161			HYD	34	47	46	127
3	KNL	41	50	60	151			KNL	45	53	59	157
4	HYD	24	45	46	114
5	HYD	32	45	39	116			HYD	31	45	46	122
6	KNL	46	39	55	141			KNL	44	48	57	150
7	HYD	36	54	43	132
8	HYD	34	41	57	133
9	HYD	31	40	44	115
10	KNL	49	49	61	159
11	KNL	41	42	54	138
Averages (3)

Cell Formulas
Range	Formula
I2:L3	I2	=LET(rws,$A$2:$A$11=$H2,ROUND(AVERAGE(TAKE(SORTBY(FILTER(B$2:B$11,rws),FILTER($E$2:$E$11,rws),-1),3)),0))
I5:L6	I5	=LET(rws,$A$2:$A$11=$H5,ROUND(AVERAGE(TAKE(SORTBY(FILTER(B$2:B$11,rws),FILTER($E$2:$E$11,rws),-1),5)),0))

 

[rexysri]

You may not have it yet but the new TAKE() function allows a slightly better way than INDEX/SEQUENCE to 'take' the first 3 (or 5) values to calculate with.

22 04 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	KNL	44	61	57	161			HYD	34	47	46	127
3	KNL	41	50	60	151			KNL	45	53	59	157
4	HYD	24	45	46	114
5	HYD	32	45	39	116			HYD	31	45	46	122
6	KNL	46	39	55	141			KNL	44	48	57	150
7	HYD	36	54	43	132
8	HYD	34	41	57	133
9	HYD	31	40	44	115
10	KNL	49	49	61	159
11	KNL	41	42	54	138
Averages (3)

Cell Formulas
Range	Formula
I2:L3	I2	=LET(rws,$A$2:$A$11=$H2,ROUND(AVERAGE(TAKE(SORTBY(FILTER(B$2:B$11,rws),FILTER($E$2:$E$11,rws),-1),3)),0))
I5:L6	I5	=LET(rws,$A$2:$A$11=$H5,ROUND(AVERAGE(TAKE(SORTBY(FILTER(B$2:B$11,rws),FILTER($E$2:$E$11,rws),-1),5)),0))

thx for updating peter. another query .. how to average least 3 or 5 values with the same condition
can u plz tell

 

[rexysri]

thx for updating peter. another query .. how to average least 3 or 5 values with the same condition
can u plz tell

ignoring blanks cells & zeros