CODEWITHSUNDEEP
excel
Bryan
Bryan

Reputation: 3

Excel ranking based on grouping priorities

Hi everyone I have an excel question on how to rank but based first on a a ranking but then next on a second priority of a group. The formula is written in column 'Final_Rank' and I just hid a bunch of rows to show the clear example. Within the column Rank is just a normal rank function. I want the priority to be within Rank first, but then to add the next rank to the next item of the same group*. So if you look at Group HYP it will supersede ranked (3 and 4) and then 5 would be given to the next newest group.

I hope this is a clear explanation, thanks. 

Group   Rank   Final_Rank_Manual   
TAM     1         1                  
HYP     2         2                  
GAB     3         5                  
HYO     4         8                  
ALO     5         9                  
HYP     7         3                  
ACO     8         12                 
IBU     9         13                 
ACO     11        14                 
ALO     18        10                 
GAB     44        6                  
IBU     53        15                 
IBU     123       16                 
GAB     167       7                  
HYP     199       4

Upvotes: 0

Views: 1590

Answers (1)

CactusCake
CactusCake

Reputation: 990

You can do this with an extra helper column. Assuming your table currently occupies columns A-C, with one header row, put the following in C2:

=SMALL(IF($A$2:$A$6=A2,$B$2:$B$6,9999999999),1)+(B2*0.000000001)

You'll need to enter this as an array formula by using Ctrl+Shift+Enter↵. Copy it down throughout the whole column. This gives you the group's ranking, and it adds a tiny decimal indicating the individual values position within each group. (e.g. the 3rd "HYP" value is converted to something like 2.0000000199, because out of all the available values, the second lowest belongs to "HYP", and this specific "HYP" value is 199).

Next, enter the following in D2 and copy it down throughout the column:

=RANK(C2,$C$2:$C$6,1)

This will give you the "Final" rankings. There won't be any ties because of the tiny decimals we added in the previous formula. The results end up looking just like your sample.

Upvotes: 1

Related Questions