Breaking Ties in Ranking

[Zainuddin Zakaria]

I want to RANK students' position in class according to their "Total Mark"
in Column AN.
I use the following formula to do the ranking.

=RANK($AN$7:$AN$56,$AN$7:$AN$56)

If the "Total Mark" is the same for two or more students, then RANKING must
now be based on their 'Grade Point Average' which is listed in in Column AM.

If the 'Grade Point Average' is also the same, the rank should now be based
on marks the students score for COURSE A in Column AL.

Can someone suggest a formula for that?

Thank you in advance.

 

[Peter Aitken]

I want to RANK students' position in class according to their "Total Mark"
in Column AN.
I use the following formula to do the ranking.

=RANK($AN$7:$AN$56,$AN$7:$AN$56)

If the "Total Mark" is the same for two or more students, then RANKING
must
now be based on their 'Grade Point Average' which is listed in in Column
AM.

If the 'Grade Point Average' is also the same, the rank should now be
based
on marks the students score for COURSE A in Column AL.

Can someone suggest a formula for that?

Thank you in advance.

First of all, the formula you give won't work. The first argument has to be
the single number you want to rank, not a list.

I think you will have to create two ranks, then combine them.

 

[Chip Pearson]

See http://www.cpearson.com/excel/rank.htm .

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[random1970]

Zainuddin,

Add a column before the one you're currently ranking in. Let's assume
the other two scores for ranking are in columns AO and AP, and change
your current formula to this one.
=RANK(AN7,$AN$7:$AN$56)+rank(AO7,$AO$7:$AO$56)/100+rank((AP7,$AP$7:$AP$56)/10000)

This will give a number like 1.0345, indicating a rank of 1 in the
first criteria, 3 in the second criteria (first two decimal places) and
45 in the last (3rd and 4th decimal places).

Then use the new column you added to rank these scores. This will give
you your overall rank, with no ties!!

e.g If a student was ranked 4th, 5th and 12th, and another student
ranked 4th, 5th and 11th, their combined numerical ranks would be
4.0512 and 4.0511. The latter would rank first, as you want it to.

Hope it helps.

 

[Zainuddin Zakaria]

Thank you so much Random1970. I appreciate your kind help. I will try it out
in a short while.