Assigning Rank for Pass, and 0 for Fail

[Muthukrishnan V]

Book1
	A	B	C	D	E
2		Excel 365
3		Mark%	Result	Rank
4		81%	Pass	1
5		75%	Pass	2
6		42%	Fail	0
7		74%	Pass	3
8		48%	Fail	0
9
10		Rank for Pass
11		0 for Fail
12		Request formula D4:D8
Sheet1

 

[Peter_SSs]

Like this?

24 11 10.xlsm
	B	C	D
3	Mark%	Result	Rank
4	81%	Pass	1
5	75%	Pass	2
6	42%	Fail	0
7	74%	Pass	3
8	48%	Fail	0
Rank Pass

Cell Formulas
Range	Formula
D4:D8	D4	=RANK(B4,B$4:B$8)*(B4>0.5)

 

[Sergius]

Hello! This should help.

Виолетта_УФ.xls
	A	B	C	D
2		Excel 365
3		Mark%	Result	Rank
4		0,81	Pass	1
5		0,75	Pass	2
6		0,42	Fail	0
7		0,74	Pass	3
8		0,48	Fail	0
9
10		Rank for Pass
11		0 for Fail
12		Request formula D4:D8
Sheet1

Cell Formulas
Range	Formula
D4:D8	D4	=IF($C4<>"Fail",RANK($B4,$B$4:$B$8),0)

 

[Muthukrishnan V]

Thank you Mr Peter sir and Mr Sergius sir,
I am grateful to our Forum

 

[Muthukrishnan V]

Sirs, I have further request on the same subject. Kindly advise me formula. Minimum 50 for PASS in each test.

Rank of Pass candidates.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Candidate	Test1	Test2	Test3	Test4	Test5	Total	Mark%	Result	Grade	Rank	My Request
2	A	100	100	100	100	100	500	100%	Pass	Distinction	1	1
3	B	11	10	22	43	14	100	20%	Fail	No grade	0
4	C	66	99	76	88	80	409	82%	Pass	A	4	3
5	D	50	50	50	50	50	250	50%	Pass	C	0	8
6	E	74	61	56	50	64	305	61%	Pass	C	9	6
7	F	70	70	70	72	70	352	70%	Pass	B	7	5
8	G	62	53	52	81	54	302	60%	Pass	C	10	7
9	H	46	100	100	93	82	421	84%	Fail	No grade	3
10	I	100	87	39	26	75	327	65%	Fail	No grade	8
11	J	89	78	61	98	76	402	80%	Pass	A	5	4
12	K	100	100	10	94	70	374	75%	Fail	No grade	6
13	L	55	63	44	67	54	283	57%	Fail	No grade	11
14	M	85	91	95	72	90	433	87%	Pass	A	2	2
15	N	66	43	41	65	50	265	53%	Fail	No grade	12
16
17					Excel 365
18					Minimum Pass mark: 50 in each test
19			My Request:		Column L to show Rank in PASS order
20					Kindly advise formula for Column L
Sheet1

Cell Formulas
Range	Formula
G2:G15	G2	=SUM(B2:F2)
H2:H15	H2	=SUM(B2:F2)/500
I2:I15	I2	=IFS(MIN(B2:F2) >= 50, "Pass", TRUE, "Fail")
J2:J15	J2	=IF($I2="Pass",IFS($H2>=$T$38,$S$38,$H2>=$T$39,$S$39,$H2>=$T$40,$S$40,$H2>=$T$41,$S$41),"No grade")
K2:K15	K2	=RANK(H2,H$2:H$15)*(H2>0.5)
E3	E3	=SUM(B3:D3)
F2:F4,F9:F15,F6:F7	F2	=AVERAGE(B2:D2)

 

[Phuoc]

Try this in K2:

=IF(I2<>"Pass",0,COUNTIFS($I$2:$I$15,"Pass",$H$2:$H$15,">"&H2)+1)

 

[Peter_SSs]

Try this

24 11 10.xlsm
	H	I			L
1	Mark%	Result			My Request
2	100%	Pass			1
3	20%	Fail
4	82%	Pass			3
5	50%	Pass			8
6	61%	Pass			6
7	70%	Pass			5
8	60%	Pass			7
9	84%	Fail
10	65%	Fail
11	80%	Pass			4
12	75%	Fail
13	57%	Fail
14	87%	Pass			2
15	53%	Fail
Rank Pass (2)

Cell Formulas
Range	Formula
H2:H15	H2	=SUM(B2:F2)/500
I2:I15	I2	=IFS(MIN(B2:F2) >= 50, "Pass", TRUE, "Fail")
L2:L15	L2	=IF(I2="Pass",MATCH(H2,SORT(FILTER(H$2:H$15,I$2:I$15="Pass"),,-1),0),"")

 

[Muthukrishnan V]

Great. Works fine. Thank you Mr Phuoc sir and Mr Peter sir