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

Alternative

to be used for earlier versions

in C5:

=IF(ISBLANK(C1), NA(), SUM(--(FREQUENCY(($C1:$M1<C1)*$C1:$M1, ($C1:$M1<C1)*$C1:$M1) > 0)))