Rank Formula With Tie Break

[CarlCarlos]

Dear all,

I am trying to comu up with a formulat that ranks by the 'number of passes' (highest being best) there are only 11 maximum passes. The reality of this is that 40 out of a class of 100 will end up with this.

I therefore want to tie break, so rather than have 40 students as rank 1, i want to 'double rank' on 'Score' as the secondary rank.

is this possible:

Student No of Passes Score RANK (im hoping for)

A 11 100 1
B 10 110 3
C 11 20 2
D 9 150 4

 

[jasonb75]

Something like

=RANK(B1,$B$1:$B$4,0)+COUNTIF($B$1:$B1,B1)-1

 

[AlphaFrog]

Or this...

Excel Workbook
	A	B	C	D	E
1	Student	Passes	Score	Rank	Passes-Score
2	A	11	100	1	11.100
3	B	10	110	3	10.110
4	C	11	20	2	11.020
5	D	9	150	4	9.150
Sheet

 

[barry houdini]

Try

=RANK(B1,B$1:B$4)+SUMPRODUCT((B$1:B$4=B1)*(C$1:C$4>C1))