Rank values and apply random rank to duplicates

avg1995

New Member
Joined
May 31, 2017
Messages
10
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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))

<tbody>
</tbody>

Godspeed!
 
Upvote 0
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))

<tbody>
</tbody>

Godspeed!

Thanks for the help!
This returns 0 for every item when I copy it accross. Any suggestions?
 
Upvote 0
=
f
g
h
i
j
k
4
45
40
42
45
42
41
5
1​
6​
3​
2​
4​
5​

<tbody>
</tbody>

f5=RANK(F4,$F$4:$K$4)+COUNTIF($F$4:F4,F4)-1 copy across
 
Upvote 0
Yes, this one determines which items are duplicates, but ranks each set of duplicates the same.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.

341526

<tbody>
</tbody>
432349753349652178

<tbody>
</tbody>

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!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top