Thanks:  0
Likes:  0

# Thread: Rank values and apply random rank to duplicates

1. ## Rank values and apply random rank to duplicates

In the cells F4:K4 I have 6 values that are formulated from elsewhere in my workbook. I want to rank these values from 1-6, which is simple using an =RANK. My issue is that sometimes two of these values could be the same. Is there a way to rank all values, but if there are duplicates assign them a random rank that isn't used by another number in the range?

2. ## Re: Rank values and apply random rank to duplicates

Maybe...

 F G H I J K 4 45 40 42 45 42 41 5 1 6 3 2 4 5

Formula in F5 copied across
=COUNTIF(\$F4:\$K4,">"&F4)+COUNTIF(\$F4:F4,F4)

M.

3. ## Re: Rank values and apply random rank to duplicates

Hello, avg1995!

Try this and copy across:

 =IF(COUNTIF(\$E4:E4,F4)>0,RANK(F4,\$F\$4:\$K\$4)+1,RANK(F4,\$F\$4:\$K\$4))

Godspeed!

4. ## Re: Rank values and apply random rank to duplicates

Originally Posted by estevaoba
Hello, avg1995!

Try this and copy across:

 =IF(COUNTIF(\$E4:E4,F4)>0,RANK(F4,\$F\$4:\$K\$4)+1,RANK(F4,\$F\$4:\$K\$4))

Godspeed!
Thanks for the help!
This returns 0 for every item when I copy it accross. Any suggestions?

5. ## Re: Rank values and apply random rank to duplicates

=
 f g h i j k 4 45 40 42 45 42 41 5 1 6 3 2 4 5

f5=RANK(F4,\$F\$4:\$K\$4)+COUNTIF(\$F\$4:F4,F4)-1 copy across

6. ## Re: Rank values and apply random rank to duplicates

@avg1995

Have you seen/tried the formula in post #2 ?

M.

7. ## Re: Rank values and apply random rank to duplicates

Yes, this one determines which items are duplicates, but ranks each set of duplicates the same.

8. ## Re: Rank values and apply random rank to duplicates

The outcome i showed in post #2 (F5:K5) is not what you want? If not, could you, please, provide a data sample along with desired results?

M.

@avg1995

Post#5 ?

10. ## Re: Rank values and apply random rank to duplicates

Originally Posted by avg1995
Thanks for the help!
This returns 0 for every item when I copy it accross. Any suggestions?
I don't get it! I tested and retested and that formula worked just fine here.
The figures below, for example, were ranked as desired, despite the repetition.

 3 4 1 5 2 6
 432 349 753 349 652 178

The formula is built so that, when COUNTIF is greater than 0, i.e, when a value repetition occurs, 1 is added to RANK function result.
Please make sure the cells with data are addressed correctly in your sheet.

Godspeed!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•