Formula to calculate Rank based on number of failures

[avijit74]

Hi !
Please suggest me a formula to calculate the Ranks of a range of Scores based on the number of failures.
Regards,
avijit74

[Ace_XL]

Try in E1

=SUMPRODUCT(--($B$2:$B$15<B2))+SUMPRODUCT(($B$2:$B$15=B2)*($C$2:$C$15>C2))+1

Copy down

[avijit74]

Hi Ace_XL,
Thanks a lot. I want to know what if any two, may be more, Scores and their Failures are same ? Here both in A7 & A8 are assigned Rank 6. In this case I want Rank 6 for the student in A8 and Rank 7 for A7 ie, in alphabetical order. Is this possible ?
Regards,
Avijit74

[Ace_XL]

I'd suggest sorting your list alphabetically and then using an additional logic in your formula

=SUMPRODUCT(--($B$2:$B$15<B2))+SUMPRODUCT(($B$2:$B$15=B2)*($C$2:$C$15>C2))+1+COUNTIFS($B$1:$B1,B2,$C$1:$C1,C2)