Ranking using 2 values equally important

[darkpassenger]

Hey folks!
Looking to rank students using 2 scores (%) that are equally important. Should there be any ties in the ranking, the formula should be able to determine how to break the ties.

Student Score 1 Score 2
Student 1 68% 51.4%
Student 2 70% 37.0%
Student 3 100% 5.5%
Student 4 67% 30.7%
Student 5 81% 14.6%
Student 6 73% 19.3%
Student 7 39% 52.6%
Student 8 48% 30.1%
Student 9 45% 23.5%
Student 10 33% 15.4%
Student 11 22% 24.1%
Student 12 8% 36.6%
Student 13 24% 20.6%
Student 14 18% 16.7%
Student 15 0% 23.5%
Student 16 0% 6.3%
Student 17 0% 2.2%

Thank in advance !

[daddylonglegs]

I suggest you add another column with the sum of the 2 scores and then rank that column

[darkpassenger]

I suggest you add another column with the sum of the 2 scores and then rank that column

Thanks for your response however would prefer not to add percentages and rank based on that total. Any other suggestion?

[arrowmaker]

Do you want someone ranked say 1st and then 15th to equal someone ranked 8th and 8th. (adding ranks together)

What criteria do you want to break any tie ?

[daddylonglegs]

If the scores are equally important then it would seem to me that summing the scores is the best way because that would do exactly what you asked, rank with equal importance.

If you don't wish to do that then perhaps I don't fully understand what you are asking? Would you prefer a formula that would rank the totals within the formula without having an added column or are you trying to rank some other way?

This formula will rank the totals without duplicate ranks, without physically adding another column

=SUMPRODUCT((B2+C2<B$2:B$18+C$2:C$18)+0)+SUMPRODUCT((B2+C2=B$2:B2+C$2:C2)+0)

If that doesn't give the results you want then please suggest expected results

[kvsrinivasamurthy]

In C2 then drag down

=SUMPRODUCT(--($B$2:$B$18+$C$2:$C$18+(ROW($A$2:$A$18)*10^-4)>B2+C2+(ROW()*10^-4)))+1

[FlameRetired]

If I understand correctly this is another way.

=INDEX(FREQUENCY($B$2:$B$18+$C$2:$C$18,B2+C2),2)+1

or

=MATCH(1,FREQUENCY(B2+C2,$B$2:$B$18+$C$2:$C$18),0)