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.