Microsoft Excel - ranking by the result from multiple columns

Asked By Kesha on 03-Jan-11 07:27 PM

Hello,

i have a problem, and given up searching for the sollution. i've red lots of posts and topics on RANK function, but didn't find the answer.

i need to rank the results (people), based on their marks (in multiple columns), and need to use a tiebreaker. tiebreaker should be familiar to Formula1 (or other sport LOL) position calculator. I mean, that if two or more persons have the same result, than better position is given to the one, who has more higher positions in all the collums. i hope that you will understand what i need.

here is the sample table. RANK of "CCC" should be 2, because he has less marks "1" than "BBB". Rank of "EEE" should be 4, because he has marks 1, 3, 5... while "AAA", has 1, 3, 4.

Name	Marks	Marks	Marks	Marks	Marks	Sum	Rank
AAA	1	4	6	3	6	20	3
BBB	2	3	3	1	1	10	1
CCC	3	1	2	2	2	10	1
DDD	4	2	4	6	4	20	3
EEE	5	6	1	5	3	20	3
FFF	6	5	5	4	5	25	6

The thing is that, the lower the mark - the higher the rating, it is like positional list.

thank You in advance.

Anoop S replied to Kesha on 03-Jan-11 11:28 PM

You can use this for descending ranking

=COUNT($A$1:$A$5)-(RANK(A1,$A$1:$A$5)+COUNTIF($A$1:A1,A1)-1)+1

The formula works in the following manner. The total number of elements minus the descending rank of a value is that value's ascending rank as if the rankings were 0-based rather than 1-based

Jackpot . replied to Kesha on 04-Jan-11 01:43 AM

Hi Kesha

Another approach. Check out the below and see whether that helps.

--Instead of SUM() we will have the below formula which will sum the marks and add the lowest 3 marks.. In cell G2 enter the below formula and copy down as required



=SUM(B2:F2)+SUM(SMALL(B2:F2,{1,2,3}))

--In cell H2 enter the below RANK() formula and copy down as required...



=RANK(G2,$G$2:$G$7,1)

Use row_num() to get the ranking order as you expected.

Lalitha Kumaran replied to Kesha on 04-Jan-11 01:51 AM

select name,m1,m2,m3,m4,m5,summ,
row_number() over(order by summ) as 'rank'
from studmarks order by name

Rolf Jaeger replied to Kesha on 04-Jan-11 02:01 AM

Hi Kesha:

an alternative way of dealing with your scoring problem would be to sum the squares of the marks, using the SUMSQ function. Doing so will automatically produce the ranking order you are hoping to obtain. Give it a try.

Hope this helped,
Rolf

Jackpot . replied to Rolf Jaeger on 04-Jan-11 02:05 AM

Hi Rolf

As per OP "RANK of "CCC" should be 2, because he has less marks "1" than "BBB".

SUMSQ() will return Rank 1 for "CCC"