RANK IN SEQUENCE

D

Douglas Eckert

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
 
D

Douglas Eckert

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

Solution to tricky problem of showing exactly Top N rows in Pivottable 0
Rank Formula 1
Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
Adding sequence numbers to paragraphs 0
Rank - Change the Rank of a Tie 1
Rank Tie 6
How to Rank Name? 4
Ties using Rank Function 3

Top