Google Sheets: Dense Ranking from sorted values

Question

I have a simple table with 3 columns:

[Name] [Score] [Rank]

For the 3rd column, I'm using the following formula to rank each row according to the score:

=RANK(C9,$C$9:$C$28,0)

The problem is that the formula isn't returning the values I'd expect. For example on the last row it returns 19 when it should be 5.

I found other formulas for ranking (RANK.EQ, etc.) but same issue happens.

Here is the Google Sheet to see it in context:

https://docs.google.com/spreadsheets/d/1P1m7UHPPIcQLQkzpnk-SI1y7-0mhKytCWDjA6FJzFrM/edit?usp=sharing

Any guidance appreciated

Answer 1

The results you want can be achieved with a simple MATCH formula:

=match(round(C9,0),NamedRange1,0)

Provided an array (named NamedRange1 for above) is created, say with:

=sort(unique(round(C9:C28,0)),1,0)

Answer 2

I think the result is as intended. Check this Ranking Wikipedia page (called 'standard competition ranking'). It says:

Standard competition ranking ("1224" ranking)

In competition ranking, items that compare equal receive the same ranking number, and then a gap is left in the ranking numbers. The number of ranking numbers that are left out in this gap is one less than the number of items that compared equal. Equivalently, each item's ranking number is 1 plus the number of items ranked above it. This ranking strategy is frequently adopted for competitions, as it means that if two (or more) competitors tie for a position in the ranking, the position of all those ranked below them is unaffected (i.e., a competitor only comes second if exactly one person scores better than them, third if exactly two people score better than them, fourth if exactly three people score better than them, etc.).

Thus if A ranks ahead of B and C (which compare equal) which are both ranked ahead of D, then A gets ranking number 1 ("first"), B gets ranking number 2 ("joint second"), C also gets ranking number 2 ("joint second") and D gets ranking number 4 ("fourth").

What you want is 'dense ranking' and it can be achieved by pnuts's answer or something like this:

1. set G9 to 1
2. set G10 to =if(round(C10,0)<round(C9,0), G9+1, G9)
3. copy G10 and paste it into G11:G28

Sample sheet is here.

Answer 3

Thanks to @pnuts and @sangboklee for your solutions. I think I have a good solution now. It is pnuts's solution, just simplified:

=match(round($C9,0),sort(unique(round($C$9:$C$28,0)),1,false),0)

This essentially "embeds" the created array within a single formula, that can be applied to all rows. And as a bonus, the values don't even have to be sorted.

Please check for correctness folks, but I think this works. I've updated the linked Google Sheet from the original question description (it's "Solution 2b").