Find Rank

[Robin_]

How to find the rank of only passed students.
Here is a part of Mark sheet I prepared.
I need to find the rank of only passed students.

That means, in my problem,
ABC should get 2
DEF should get 0 or "FAIL" or "No Rank"
GHI should get 3
and JKL should get 1

In this problem, there is no incidence of tie. But, in my real problem, there can occur tie. And, for tie, the rank version of 1, 2, 3, 3, 5, 6 is okay.

Name	Total	Result	Rank
ABC	89	PASS
DEF	45	FAIL
GHI	85	PASS
JKL	95	PASS

<tbody>
</tbody>

Thanks in advance.

 

[Robin_]

Maybe...

	A​	B​	C​	D​
1​	Name​	Total​	Result​	Rank​
2​	ABC​	89​	PASS​	2​
3​	DEF​	45​	FAIL​	No Rank​
4​	GHI​	85​	PASS​	3​
5​	JKL​	95​	PASS​	1​

<tbody>
</tbody>

Formula in D2 copied down
=IF(C2="FAIL","No Rank",COUNTIFS(B:B,">"&B2,C:C,C2)+1)

Hope this helps

M.

What if, I have different way to award rank for ties?

I required to rank as following.

Excel and above formula as well provides the rank like this => 1, 1, 3, 4, 5 for two first values in tie.
But, I need like this..... 1, 1, 2, 3, 4

How that can be done?

 

[Aladin Akyurek]

What if, I have different way to award rank for ties?

I required to rank as following.

Excel and above formula as well provides the rank like this => 1, 1, 3, 4, 5 for two first values in tie.
But, I need like this..... 1, 1, 2, 3, 4

How that can be done?

Care to provide an appropriate sample along with the desired ranking?

 

[Robin_]

How to find the rank of only passed students.
Here is a part of Mark sheet I prepared.
I need to find the rank of only passed students.

That means, in my problem,
ABC should get 2
DEF should get 0 or "FAIL" or "No Rank"
GHI should get 3
and JKL should get 1

In this problem, there is no incidence of tie. But, in my real problem, there can occur tie. And, for tie, the rank version of 1, 2, 3, 3, 5, 6 is okay.

Name	Total	Result	Rank
ABC	89	PASS
DEF	45	FAIL
GHI	85	PASS
JKL	95	PASS

<tbody>
</tbody>

Thanks in advance.

Hi Aladin Akyurek,

If I have understood correctly, you need sample data for the ranking. And, the above quote is my real question.
I found answer of Marcelo useful. Which was to be calculated with the formula below.
=IF(C2="FAIL","No Rank",COUNTIFS(B:B,">"&B2,C:C,C2)+1)

This formula will award rank for ties as follows.
If two values are equal and lies in 2nd position, then ranking will be 1, 2, 2, 4, 5

But, now, I need, the ranking as 1, 2, 2, 3, 4 for two tie value in 2nd position.

Hope, I made it clear.

Thanks.

 

[Aladin Akyurek]

Hi Aladin Akyurek,

If I have understood correctly, you need sample data for the ranking. And, the above quote is my real question.
I found answer of Marcelo useful. Which was to be calculated with the formula below.
=IF(C2="FAIL","No Rank",COUNTIFS(B:B,">"&B2,C:C,C2)+1)

This formula will award rank for ties as follows.
If two values are equal and lies in 2nd position, then ranking will be 1, 2, 2, 4, 5

But, now, I need, the ranking as 1, 2, 2, 3, 4 for two tie value in 2nd position.

Hope, I made it clear.

Thanks.

No. As far as I know the formula gives 2, No rank, 3, 1 for the sample you quote, while I'm asking for a sample where you "need, the ranking as 1, 2, 2, 3, 4 for two tie value in 2nd position" instead of the set 1, 2, 2, 4, 5 you state the Branco formula would deliver.

 

[Robin_]

Ohh I got you now.

That was only a sample problem exactly same as I have here in real.
So, I can extend the same question as per my requirement.

Name	Total	Result	Rank
ABC	99	PASS	1
DEF	89	PASS	2
GHI	89	PASS	2
JKL	40	FAIL	No Rank
MNO	85	PASS	3
PQR	75	PASS	4

<tbody>
</tbody>

Here, I need the rank....... 1, 2, 2, No Rank, 3, 4.

Now, How can Marcelo's formula be edited/corrected or we construct any new formula?

Please help.

 

[shg]

	A​	B​	C​	D​	E​
1​	Name​	Total​	Result​	Rank​	​
2​	Alan	99​	PASS	1​	D2: =IF(C2 <> "pass", "No rank", SUMPRODUCT((B2 < B$2:B$7) * ($C$2:$C$7 = "pass") / (COUNTIF(B$2:B$7, B$2:B$7 & "")))+1)
3​	Barb	89​	PASS	2​
4​	Cain	89​	PASS	2​
5​	Dana	40​	FAIL	No rank
6​	Eric	85​	PASS	3​
7​	Fran	75​	PASS	4​

 

[Aladin Akyurek]

Ohh I got you now.
...

A bit late though...

A different take (possibly faster)...

D2, control+shift+enter, not just enter, and copy down:

=IF($C2="pass",SUM(IF(FREQUENCY(IF($C$2:$C$7="pass",
  IF($B$2:$B$7>$B2,$B$2:$B$7)),$B$2:$B$7),1))+1,"No Rank")

 

[Robin_]

Thank you Aladin and shg for your kind help.

Both of the formula worked fine.

Thanks again.