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

Post a Comment

Popular posts from this blog

Capture and play voice with Asterisk ARI -

-
there channel ari demo in wich can control channel state: ring, answer, play silence, play tone or audio-file ( https://github.com/asterisk/ari-examples/tree/master/channel-state , https://wiki.asterisk.org/wiki/display/ast/ari+and+channels%3a+manipulating+channel+state ) possible receive chunks (parts, buffers, etc.) of call voice (which created remote subscriber) or write chunks of voice, example array of bytes (not file) in audio format (alaw, ulow etc). you can use asterisk eagi interface voice data. other option use record or mixmonitor app record channel(channel have put stasis allow dialplan control ari) "write chunks of voice" can done application playback also can create own application using c/c++, compile asterisk , result want. no, can't redirect voice directly using ari.
Read more

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

-
i spotted odd behavior in hsql, seems when using database transactions database contraints not checked during sql inserts during sql commits , when transaction rollbacked not checked @ all. i have spring integration test: @runwith(springjunit4classrunner.class) @transactionconfiguration(defaultrollback=true, transactionmanager="transactionmanager") @transactional public class integrationtest { with test creates new entity instance , calls hibernate's persist on it. it works fine, when change defaultrollback false fails: caught exception while allowing testexecutionlistener [org.springframework.test.context.transaction.transactionaltestexecutionlistener@517a2b0] process 'after' execution test: method [public void myintegrationtest.test()], instance [myintegrationtest@546e61d5], exception [null] org.springframework.dao.dataintegrityviolationexception: not execute statement; sql [n/a]; constraint [null]; nested exception org.hibernate.exception.const...
Read more

user interface - Ignoring <Button-1> when <Key> is pressed Tkinter, Python -

-
i want swop tiles on board (in game fifteen) inputted keys. works up/down/right/left, not digit keys 1...9, though coordinates updated correctly within play() function, still doesn't seem move on board. full code is: http://pastebin.com/kvl2jf1g tips?? this key event: def key(event): if event.char.isdigit(): j, row in enumerate(board): i, char in enumerate(row): if char.get() == event.char: print "pressed", repr(event.char),i, j play(i,j) now main part in play() changes string_vars in each label swops empty label (blankx,y coordinates empty space): def play(i,j): global blankx, blanky, game_running if game_running: # update vars if tile can moved if (blankx, blanky) in [(i+1,j),(i-1,j),(i,j+1),(i,j-1)]: board[blanky][blankx].set(board[j][i].get()) board[j][i].set(' ') blanky = j blankx = this visual...
Read more