Help with ranking

thousand

New member
Joined
Apr 2, 2017
Messages
30
Reaction score
0
Points
0
Excel Version(s)
2010
Are there any ways to rank this to show all 65 as 1, all 7 as 2 and all 6 as 3 etc...?

Thanks in advance
[FONT=Arial Mäori]65[/FONT]
[FONT=Arial Mäori]65[/FONT]
[FONT=Arial Mäori]65[/FONT]
[FONT=Arial Mäori]65[/FONT]
[FONT=Arial Mäori]7[/FONT]
[FONT=Arial Mäori]7[/FONT]
[FONT=Arial Mäori]7[/FONT]
[FONT=Arial Mäori]7[/FONT]
[FONT=Arial Mäori]7[/FONT]
[FONT=Arial Mäori]7[/FONT]
[FONT=Arial Mäori]7[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
[FONT=Arial Mäori]6[/FONT]
 
I will assume that your numbers are in column A. I will also assume that your numbers are always sorted in descending order as you have them. Put a 1 in cell b1. Put the following formula in b2 and copy it down:
IF(A2=A1,B1,B1+1).

The rank.eq formula could be used if the numbers are not in descending order. However, using this formula all of the 65's would be ranked 1, all of the 7's would be ranked would be 5 and all of the 6's would be 12. The reason all of the 7's would be 5 is because the first 7 is the 5th number.
'
 
Using your posted data in A2:A22
This regular formula, copied down, ranks each value ignoring order, but without rank gaps:
Code:
B2: =IF(A2="","",SUMPRODUCT((A2<=A$2:A$30)/COUNTIF(A$2:A$30,A$2:A$30&"")))

Is that something you can work with?
 
Another option, in B2
Code:
=IF(A2<>A1;MAX($B$1:B1)+1;B1)
 
Back
Top