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

 

[Peter_SSs]

You understand what you want but I'm afraid that I don't. That is why I asked for the expected results & an explanation of how they would be obtained manually.

 

[rexysri]

sample data.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
1															NORTH
2															PRESENT WEEK RESULT
3	Sl.No	STATE	MAT MARK	MAT RANK	PHY MARK	PHY RANK	CHE MARK	CHE RANK	TOT MARK	TOT RANK	TOT MARK2	TOT RANK2		RANK RANGE	MM	MR	PM	PR	CM	CR	TM	TR
4	1	NORTH									284	1		<=5										NORTH
5	2	NORTH	92	22	96	2	100	8	288	2	277.6	2		<=10										CHENNAI
6	3	NORTH									272	3		<=15										KA
7	4	NORTH	96	9	88	16	96	43	280	6	274.4	4		<=20										T.S
8	5	NORTH									266	5		<=30										MUMBAI
9	6	NORTH	76	183	80	46	96	43	252	49	263	6		<=40										INDORE
10	7	NORTH	92	11	80	31	88	91	260	16	260	7		<=50										A.P
11	8	NORTH	96	9	92	4	96	43	284	5	259.2	8												NAGPUR
12	9	NORTH	80	103	80	51	96	24	256	33	257.33	10												GOA
13	10	NORTH									256	10												JAIPUR
14	11	CHENNAI	84	90	84	18	96	24	264	12	255.2	11												KL
15	12	NORTH									253.33	12												WB
16	13	NORTH	80	111	80	46	96	24	256	33	256	14												BLR
17	14	NORTH									254	14												MP
18	15	NORTH	84	90	80	34	88	110	252	49	256	15
19	16	KA	80	111	88	16	100	8	268	10	256	17
20	17	NORTH									256	17
21	18	CHENNAI	84	90	80	51	96	43	260	30	256	18
22	19	NORTH									252	19
23	20	NORTH	84	65	76	73	92	76	252	45	251.2	20
24	21	NORTH									250	21
25	22	NORTH	72	252	76	73	88	91	236	83	250.4	22
26	23	T.S	88	50	88	8	80	186	256	27	249.6	23
27	24	NORTH	80	94	84	20	88	110	252	40	248.8	24
28	25	NORTH	84	65	80	46	96	24	260	20	248	25
29	26	NORTH	68	310	72	106	96	43	236	79	247.2	26
30	27	T.S	100	4	88	8	96	43	284	3	247.2	27
31	28	NORTH									248	29
32	29	NORTH									248	29
33	30	NORTH	76	145	76	61	96	24	248	49	245.33	30
34	31	NORTH	88	34	80	34	80	247	248	55	248	32
35	32	MUMBAI	88	34	68	138	88	91	244	55	244	32
36	33	NORTH									245	33
37	34	NORTH	88	41	76	88	96	43	260	27	249.33	35
38	35	NORTH									242.66	35
39	36	NORTH	88	41	76	54	96	24	260	16	244	36
40	37	NORTH									243	37
41	38	T.S	84	90	88	16	92	76	264	18	248.8	38
42	39	NORTH	80	103	72	96	80	186	232	85	240.8	39
43	40	NORTH	76	165	80	46	88	110	244	63	244	41
44	41	NORTH									236	41
45	42	T.S	84	78	88	16	88	110	260	23	241.6	42
46	43	NORTH									244	43
47	44	NORTH	84	65	72	96	92	47	248	49	236	44
48	45	CHENNAI									244	45
49	46	NORTH									240	46
50	47	NORTH	76	183	80	31	96	43	252	43	238	47
51	48	NORTH	76	204	88	8	96	43	260	22	241.6	48
52	49	MUMBAI	92	22	84	20	80	208	256	33	239	49
53	50	NORTH	84	78	88	11	96	24	268	9	237	50
54	51	NORTH									233.33	51
55	52	NORTH	88	34	76	73	92	54	256	30	235	52
56	53	T.S	88	41	92	4	92	76	272	8	234.4	53
57	54	NORTH	84	78	76	80	96	24	256	34	235.2	54
58	55	NORTH	80	103	64	236	84	137	228	110	236	55
59	56	NORTH									236	56
60	57	NORTH	96	9	88	11	100	8	284	5	230	58
61	58	NORTH									240	58
62	59	NORTH	88	50	80	27	96	24	264	11	234.4	59
63	60	NORTH	88	41	76	80	96	24	260	22	235	60
64	61	NORTH	84	65	72	117	96	43	252	45	231.2	61
65	62	NORTH									236	63
66	63	NORTH	80	103	76	61	76	254	232	88	232	63
67	64	NORTH	80	103	80	24	88	82	248	42	229.6	65
68	65	NORTH	84	78	76	73	88	110	248	55	236	65
69	66	NORTH	68	328	68	150	92	54	228	107	233	67
70	67	NORTH	80	123	80	46	84	164	244	67	234	67
71	68	NORTH									233.33	68
72	69	NORTH	72	252	76	88	88	110	236	91	234.4	69
73	70	NORTH	72	252	76	73	88	82	236	79	232	70
74	71	NORTH									238.66	71
75	72	NORTH	100	4	76	61	96	43	272	7	230	72
76	73	NORTH									228	74
77	74	NORTH	84	78	76	80	80	229	240	79	235	74
78	75	NORTH									228	76
79	76	NORTH	88	50	72	117	96	43	256	40	233	76
80	77	CHENNAI									227	78
81	78	NORTH									228	78
82	79	NORTH									230.66	79
83	80	NORTH	68	394	76	80	76	278	220	149	231	80
84	81	NORTH	88	34	68	150	96	24	252	40	225	83
85	82	MUMBAI	80	103	56	319	92	54	228	98	228	83
86	83	NORTH	76	165	80	31	84	147	240	67	228	83
87	84	T.S	92	10	76	61	92	54	260	16	227.2	84
88	85	NORTH									228	86
89	86	NORTH									224	86
90	87	NORTH									233.33	87
91	88	NORTH									225.33	91
92	89	NORTH	76	204	68	176	96	43	240	79	229	91
93	90	NORTH									224	91
94	91	NORTH	60	815	72	117	100	8	232	107	232	91
95	92	NORTH	76	183	60	296	84	164	220	149	228	92
96	93	NORTH									234	94
97	94	NORTH	72	231	76	80	80	197	228	110	228	94
98	95	T.S	88	41	88	5	84	164	260	18	225.6	95
99	96	NORTH	76	183	68	164	84	147	228	113	228	97
100	97	NORTH	68	394	68	191	88	127	224	146	230.4	97
101	98	NORTH	84	78	64	184	100	8	248	51	222.66	98
102	99	NORTH									220	102
103	100	NORTH									222	102
Sheet1

column B contains Different states,
so here we need state wide averages will be posted in column O to P

 

[rexysri]

You understand what you want but I'm afraid that I don't. That is why I asked for the expected results & an explanation of how they would be obtained manually.

 

[rexysri]

sample data.xlsx
	N	O	P	Q	R	S	T	U	V
2		NORTH
3	RANK RANGE	MM	MR	PM	PR	CM	CR	TM	TR
4	<=5	94.00		92.00		98.00		284.00	94.00
5	<=10	88.67		86.00		95.33		270.00	88.67
6	<=15	87.00		84.50		94.50		266.00	87.00
7	<=20	86.67		83.56		94.22		264.44	86.67
8	<=30	82.86		81.43		93.71		258.00	82.86
9	<=40	83.56		80.22		92.44		256.22	83.56
10	<=50	82.61		80.52		92.70		255.83	82.61
Sheet1

 

[Peter_SSs]

?

.. & an explanation of how they would be obtained manually.

 

[rexysri]

?

thank you for your patience peter

i want the averages of Math marks, Phy marks, Che marks, Top 3,5,10 (largest to smallest) values of column K, as well as averages of maths rank , phy rank, che rank based on (smallest to largest) values of column L.