Breaking Ties in Ranking

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.

"Zainuddin Zakaria" <[email protected]> wrote in message
news:[email protected]...
>
> 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.


--
Peter Aitken

Remove the crap from my email address before using.

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

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


"Zainuddin Zakaria" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
>
>
>

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.

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


"random1970" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> random1970
> ------------------------------------------------------------------------
> random1970's Profile:
> http://www.excelforum.com/member.php...o&userid=32112
> View this thread: http://www.excelforum.com/showthread...hreadid=518681
>
>