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.

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

thank You in advance.

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.

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

=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

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

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

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)

select name,m1,m2,m3,m4,m5,summ,

row_number() over(order by summ) as 'rank'

from studmarks order by name

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

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

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"

As per OP

SUMSQ() will return Rank 1 for "CCC"