Rank

[cecil]

I have a list of number like what follows
1,1,1,5,5,8,8,8,8,10,10

I want to return a rank of 1,1,1,2,2,3,3,3,3,4,4

Not 1,1,1,4,4,6,6,6,6,10,10 like the Rank function does.

How do I do this? Thanks.

[HansV]

Let's say your values are in A1:A11.
Enter the following array formula in B1, confirmed with Ctrl+Shift+Enter:

=SUM(--ISNUMBER(MATCH(ROW(INDIRECT("1:"&RANK(A1,A$1:A$11,1))),RANK(A$1:A$11,A$1:A$11,1),0)))

Fill down to B11.

[HansV]

By the way, the above complicated formula will work even if the values haven't been sorted in ascending order.
If they are guaranteed to be in ascending order, you can use a much simpler formula:
In B1, enter the value 1.
In B2, enter the formula =B1+(A2>A1)
Fill down from B2 to B11.

[dasadler]

Cecil,

I know you have the answer but with regard to your question... what is the difference between what the RANK function does and what you want? Looks the same to me.

[HansV]

If there are ties, RANK will return non-contiguous ranks, see Cecil's example. He asked for a way to return contiguous ranks.

[dasadler]

Ah - okay. Thanks. My lack of familiarity with the RANK function prompted the question. After typing in Cecil's numbers and trying it, I understand.