RANK IN SEQUENCE

G

Guest

RANK function omits the next rank number in a series after each set of tied
scores. For instance, if a cluster of scores are "2", the next ranking will
be 4 instead of 3. I want the rankings to show the next number in the rank
sequence and not to skip it. Is there a formula or a function which will
allow this?
 
T

T. Valko

With the numbers in A2:A11

This will also account for empty cells:

=IF(A2="","",SUMPRODUCT(--(A2<A$2:A$11),1/COUNTIF(A$2:A$11,A$2:A$11&""))+1)

Copy down as needed

If there won't be any empty cells:

=SUMPRODUCT(--(A2<A$2:A$11),1/COUNTIF(A$2:A$11,A$2:A$11))+1

Biff
 
G

Guest

Thank you VERY MUCH, Biff! That was beautiful!

T. Valko said:
With the numbers in A2:A11

This will also account for empty cells:

=IF(A2="","",SUMPRODUCT(--(A2<A$2:A$11),1/COUNTIF(A$2:A$11,A$2:A$11&""))+1)

Copy down as needed

If there won't be any empty cells:

=SUMPRODUCT(--(A2<A$2:A$11),1/COUNTIF(A$2:A$11,A$2:A$11))+1

Biff
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rank Formula 1
Rank Tie 5
Rank - Change the Rank of a Tie 1
Ties using Rank Function 3
How Do I Combine Rankings for Multiple Categories? 1
How to Rank Name? 4
Rank Function with Multiple Criteria 9
Need to rank values with ties 9

Top