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

Sorry Andreas,

But seems like this fan of yours and HansV was not clear enough.

Kindly see my reply to HansV.

Answer 3

Hi

in B2:

=XMATCH(A2:A10, SORT(UNIQUE(A2:A10)))

Answer 4

Looks like RANK function is not going to work here.

Anyways, have worked out the same using a combination of MATCH, SORT & UNIQUE as follows:

Thanks anyways.

If RANK function could be used in any manner, specially like for instance to be used for earlier versions, like in a UDF, your feedback would be appreciated.

Answer 5

Excellent feedback HansV!!!

Thanks a lot dear!!!