Ranking students based on their scores

Hi,

I am working on a problem that involves ranking 8 students in a class. Their aggregate marks are the first base for ranking them. However, because there is a tie and 2 students have scored the same marks, next weightage has been given to their maths score and whoever has the higher score in maths will be ranked first than the other.
I tried using the RANK function in excel, but I dont know how to put the condition of using the maths score as the second base in case of ties. Can anyone help with ideas on the formula or approach to solve this problem? You can pick up any randon scores that have atleast 1 tie and correspondingly random maths score.

Thanks
MSD

Try:

=RANK(B3,B$3:B$9)+SUMPRODUCT(--(B$3:B$9=B3),--(C3<C$3:C$9))

copied down

In case the Math score also became tied, then if you don't want the two to have same rank, try:

=RANK(B3,B$3:B$9)+SUMPRODUCT(--(B$3:B$9=B3),--(C3<C$3:C$9))+COUNTIF(C$3:C3,C3)-1

Also, excelforum123,

This is an old thread. If you have a solution to offer its fine to post it here but not otherwise