Rank Function in Excel with Duplicate Values but without skipping a rank

Question

Dear friends,

In Excel, while I use Rank Function for values like, as follows:

5

4

3

4

2

2

2

1

4

I want to rank all in a ranking manner as follows, i.e. in ascending mode, leaving any values in the outcome:

5

4

3

4

2

2

2

1

4

But the result is found to be received as:

9

6

5

6

2

2

2

1

6

In other words, 3 & 4 are not being found to be returned.

Any way to achieve the same?

Thanks in advance.

Answer 1

A formula that should work in all versions of Excel:

=IF(C1="","N/A",SUMPRODUCT(($C1:$M1<=C1)*($C1:$M1<>"")/COUNTIF($C1:$M1,$C1:$M1&"")))

Answer 2

Let's say your numbers are in A1:A9.

Enter the following formula in B1, then fill down to B9:

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

Answer 3

Or do you want to count the equal items from the bottom upwards?

B2: =RANK(A2,$A$2:$A$10,1)

C2: =RANK(A2,$A$2:$A$10,1)+COUNTIF(A2:$A$10,A2)-1
D2: =RANK(A2,$A$2:$A$10,1)+COUNTIF($A$2:A2,A2)-1

D2 is the same solution as Hans already has shown.

Andreas.

Answer 4

Thanks HansV,

Thanks for all your help, however, seems like I was not clear enough, because of using same numbers as the ranking score. The requirement is actually sharing the same rank for same number, but the next larger / smaller number being ranked the next rank.

In other words, consider another example like the following with different values Ranking Returned while Ranking Required:

Have tried a combination of Array formulae of Rank & Unique functions {like =RANK($A2,UNIQUE(IF(not(isblank($A$2:$A$10)),$A$2:$A$10)),1)} but finding the same to be leading to be not allowed and returning the error as follows:

See, if you can provide a way for the same without using a helper column.

Thanks again.