Rank Duplicate Values With Duplicate Ranks Without Skipping Sequence

[PATSYS]

Hi all,

I have the following data in A1:A7

100
40
80
80
85
60
60

In the corresponding column B, I want to return the rank of each (descending) i.e.

1
5
3
3
2
4
4

If I use the function rank, what I get is:

1
7
3
3
2
5
5

How can this be done?

Thanks

 

[T. Valko]

All you have to do is to adjust the range in the formula accordingly to your data, ie, A2:A10, not A2:A9

M.

Doh!

 

[Marcelo Branco]

Doh!

 

[proficient]

Haha

 

[labrys]

Try this...

	A	B
Value	Rank
100	1
40	5
80	3
80	3
85	2
60	4
60	4

<TBODY>
[TD="bgcolor: #cacaca"]1
[/TD]

[TD="bgcolor: #cacaca"]2
[/TD]

[TD="bgcolor: #cacaca"]3
[/TD]

[TD="bgcolor: #cacaca"]4
[/TD]

[TD="bgcolor: #cacaca"]5
[/TD]

[TD="bgcolor: #cacaca"]6
[/TD]

[TD="bgcolor: #cacaca"]7
[/TD]

[TD="bgcolor: #cacaca"]8
[/TD]

</TBODY>

This formula entered in B2 and copied down:

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

Thats great. However what happens if we were using rank in reverse (order 1) so 40 = 1 and 100 = 5?

Only way I can think of doing it is to get the max number from the rank col and minus the new rank from this number. but this would require two columns (and hide one?) which seems a little silly. But one column would be ascending rank and one would be descending.

eg - third column would be (assuming 2nd column is the ascending rank):
=(MAX([Column2])+1)-[@Column2]
or
=(MAX($B$2:$B$8)+1)-B2)
if you dont use table references

 

[Marcelo Branco]

Thats great. However what happens if we were using rank in reverse (order 1) so 40 = 1 and 100 = 5?

All you have to do is change
A2 < A$2:A$8
to
A2>A$2:A8


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Value​

[/TD]
[TD]

Rank​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

100​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

40​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

80​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

80​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

85​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

60​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

60​

[/TD]
[TD]

2​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B2 copied down
=SUMPRODUCT((A2>A$2:A$8)/COUNTIF(A$2:A$8,A$2:A$8))+1

M.

 

[labrys]

All you have to do is change A2 < A$2:A$8 to A2>A$2:A8 [TABLE="class: grid"] <tbody>[TR] [TD][/TD] [TD]

A​

[/TD] [TD]

B​

[/TD] [/TR] [TR] [TD]

1​

[/TD] [TD]

Value​

[/TD] [TD]

Rank​

[/TD] [/TR] [TR] [TD]

2​

[/TD] [TD]

100​

[/TD] [TD]

5​

[/TD] [/TR] [TR] [TD]

3​

[/TD] [TD]

40​

[/TD] [TD]

1​

[/TD] [/TR] [TR] [TD]

4​

[/TD] [TD]

80​

[/TD] [TD]

3​

[/TD] [/TR] [TR] [TD]

5​

[/TD] [TD]

80​

[/TD] [TD]

3​

[/TD] [/TR] [TR] [TD]

6​

[/TD] [TD]

85​

[/TD] [TD]

4​

[/TD] [/TR] [TR] [TD]

7​

[/TD] [TD]

60​

[/TD] [TD]

2​

[/TD] [/TR] [TR] [TD]

8​

[/TD] [TD]

60​

[/TD] [TD]

2​

[/TD] [/TR] </tbody>[/TABLE] Formula in B2 copied down =SUMPRODUCT((A2>A$2:A$8)/COUNTIF(A$2:A$8,A$2:A$8))+1 M.

Wow.. cheers! I must admit SumProduct confuses me!

 

[alaxee]

Thank you very much for the help. I have the similar problem. It is fixed now.

However, there is one more issue I am facing now. I have a list of student actually and some of them are absent in the exam. I have to explicitly mark "A" in that column. When I have "A" in the column on which the suggested formula is applied, the ranks becomes uncertain. That is it starts ranks from 2 and onward. 1st rank is skipped due to A. Rest is working fine.

Any more suggestions for the solution of this.

Thanks

 

[Marcelo Branco]

Thank you very much for the help. I have the similar problem. It is fixed now.

However, there is one more issue I am facing now. I have a list of student actually and some of them are absent in the exam. I have to explicitly mark "A" in that column. When I have "A" in the column on which the suggested formula is applied, the ranks becomes uncertain. That is it starts ranks from 2 and onward. 1st rank is skipped due to A. Rest is working fine.

Any more suggestions for the solution of this.

Thanks

See if this is ok


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Value​

[/td][td]

Rank​

[/td][/tr]

[tr][td]

2​

[/td][td]

100​

[/td][td]

1​

[/td][/tr]

[tr][td]

3​

[/td][td]

A​

[/td][td]

Absent​

[/td][/tr]

[tr][td]

4​

[/td][td]

95​

[/td][td]

4​

[/td][/tr]

[tr][td]

5​

[/td][td]

A​

[/td][td]

Absent​

[/td][/tr]

[tr][td]

6​

[/td][td]

98​

[/td][td]

2​

[/td][/tr]

[tr][td]

7​

[/td][td]

95​

[/td][td]

4​

[/td][/tr]

[tr][td]

8​

[/td][td]

95​

[/td][td]

4​

[/td][/tr]

[tr][td]

9​

[/td][td]

95​

[/td][td]

4​

[/td][/tr]

[tr][td]

10​

[/td][td]

96​

[/td][td]

3​

[/td][/tr]
[/table]

Formula in B2 copied down
=IF(ISNUMBER(A2),SUMPRODUCT(--ISNUMBER($A$2:$A$10),--(A$2:A$10>A2),1/COUNTIF(A$2:A$10,A$2:A$10))+1,"Absent")

M.

 

[alaxee]

Exactly this is what I was looking for. Bundle of thanks Marcelo. My students will surely be happy with your formula. Stay happy.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.