Dense Rank in Excel

microsoft excelmicrosoft-excel-2013

How can I get an equivalent of dense rank from SQL in Excel?

I have unsorted data (number of data varies from week to week) and the duplicates should have the same rank, i.e.

number    rank
6          1
12         2
23         3
6          1
6          1

I have found this solution

=SUMPRODUCT( (FREQUENCY($A$1:$A$10, $A$1:$A$10) > 0) * (A1 >= $A$1:$A$11) )

Note the extra row required in the second expression. 

but I cannot use it as I do not know how many entries I will have and this one assumes I know the data set a priori.

Best Answer

Use:

INDEX(A:A,MATCH(1E+99,A:A))

To set the upper bounds of the range. This will find the last cell that has a number and set that as the last cell in the range. If the column has text instead then use "ZZZ" in place of the 1E+99.

For the add one we just add one to the MATCH:

INDEX(A:A,MATCH(1E+99,A:A)+1))

So the whole formula will be:

=SUMPRODUCT( (FREQUENCY($A$1:INDEX(A:A,MATCH(1E+99,A:A)), $A$1:INDEX(A:A,MATCH(1E+99,A:A))) > 0) * (A1 >= $A$1:INDEX(A:A,MATCH(1E+99,A:A)+1)) )

This is now dynamic, as values are added or removed from column A the reference will change accordingly.

enter image description here

Related Question