Retrive Median Value bases on Criteria

[Sufiyan97]

I have below table and expected results are given in column K

Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Group A		Group A	Match 1	Match 2	Total
2	Group A	60	R	30	30	60			Match 13
3	Group A	57	J	27	30	57
4	Group A	49	M	25	24	49			Group A	60	Manual		A's Top
5
6	Group B		Group B						Group B	58	Expected		B's Second
7	Group B	57	K	32	25	57
8	Group B	58	A	31	27	58	This should be the result		Group C	78	Expected		C's second
9	Group B	59	V	31	28	59
10
11	Group C		Group C
12	Group C	60	K	29	30	78	This should be the result
13	Group C	61	A	31	29	79
14	Group C	62	V	25	24	75
15
16
17
Absolute Points

Cell Formulas
Range	Formula
B2:B4,B7:B9	B2	=+F2
F2:F4,F7:F9	F2	=+D2+E2
A2:A14	A2	=IF(C2="","",IF(ISNUMBER(SEARCH("Group",C2)),C2,A1))

 

[Sufiyan97]

I tried and got it worked for me, if someone has better idea than this for Office 2013 then please suggest

Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Group A		Group A	Match 1	Match 2	Total
2	Group A	60	R	30	30	60			Match 13
3	Group A	57	J	27	30	57
4	Group A	49	M	25	24	49			Group A	60	Manual
5
6			Group B						Group B	58	Expected
7	Group B	57	K	32	25	57
8	Group B	58	A	31	27	58	This should be the result		Group C	78	Expected
9	Group B	59	V	31	28	59
10
11			Group C
12	Group C	60	K	29	30	78	This should be the result
13	Group C	61	A	31	29	79
14	Group C	62	V	25	24	75
15
16
17
Sheet2

Cell Formulas
Range	Formula
B3:B4,B7:B9	B3	=+F3
F2:F4,F7:F9	F2	=+D2+E2
J6:J8	J6	=IFERROR(MEDIAN(IF($A$2:$A$14=I6,($F$2:$F$14)/($F$2:$F$14<>0),"")),"")
A2:A15	A2	=IF(C2="","",IF(ISNUMBER(SEARCH("Group",C1)),C1,A1))

 

[shift-del]

Hello

Mappe12
	A	B	C	D	E	F	G	H	I	J	K
1	Group	Player	Match 1	Match 2	Total			Group	Median	Median	Median
2	A	R	30	30	60			A	57	57	57
3	A	J	27	30	57			B	58	58	58
4	A	M	25	24	49			C	59	59	59
5	B	K	32	25	57
6	B	A	31	27	58
7	B	V	31	28	59
8	C	K	29	30	59
9	C	A	31	29	60
10	C	V	25	24	49
Tabelle3

Cell Formulas
Range	Formula
H2:H4	H2	=UNIQUE(A2:A10)
I2:I4	I2	=AGGREGATE(17,6,($E$2:$E$10)/($A$2:$A$10=H2),2)
J2:J4	J2	=AGGREGATE(19,6,($E$2:$E$10)/($A$2:$A$10=H2),2)
K2:K4	K2	=MEDIAN(IF($A$2:$A$10=H2,$E$2:$E$10))
E2:E10	E2	=C2+D2
Dynamic array formulas.

 

[Sufiyan97]

Thank you very much Shift-Del.

Can you please explain what 17 and 19 QURARTILE INC and EXC function does.

 

[shift-del]

All three formula calculate the median.
Look at the help articles for AGGREGATE(), QUARTILE.INC() and QUARTILE.EXC().