Excel ranking based on grouping priorities -


hi have excel question on how rank based first on a ranking next on second priority of group. formula written in column 'final_rank' , hid bunch of rows show clear example. within column rank normal rank function. want priority within rank first, add next rank next item of same group*. if @ group hyp supersede ranked (3 , 4) , 5 given next newest group.

i hope 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                   

you can helper column. assuming table occupies columns a-c, 1 header row, put following in c2:

=small(if($a$2:$a$6=a2,$b$2:$b$6,9999999999),1)+(b2*0.000000001) 

you'll need enter array formula using ctrl+shift+enter↵. copy down throughout whole column. gives group's ranking, , adds tiny decimal indicating individual values position within each group. (e.g. 3rd "hyp" value converted 2.0000000199, because out of available values, second lowest belongs "hyp", , specific "hyp" value 199).

next, enter following in d2 , copy down throughout column:

=rank(c2,$c$2:$c$6,1) 

this give "final" rankings. there won't ties because of tiny decimals added in previous formula. results end looking sample.


Comments

Popular posts from this blog

Capture and play voice with Asterisk ARI -

java - Why database contraints in HSQLDB are only checked during a commit when using transactions in Hibernate? -

visual studio - Installing Packages through Nuget - "Central Directory corrupt" -