ranking using multiple criteria
Posted on 2011-03-13
I'm trying to rank five objects, call them objects a, b, c, d and e. Each object has two "scores". Cells a1, b1, c1, d1 and e1 hold one set of scores for objects a, b, c, d and e respectively. These scores are either an H (for 'high'), an M (for 'medium') or an L (for 'low'). Cells g1, h1, i1, j1 and k1 held the second set of scores. These are numbers, with a higher number being a better score. There are multiple sets of object, ie multiple rows of data.
I'll rank the objects on the first set of scores (columns a thru e). All objects with an H get the highest rank, and among those, the objects should be ranked according to the second set of scores. Then, all objects with an M are ranked according to their second set of scores, and finally the Ls are ranked according to their scores. For example,
The objects are: a, b, c, d, e
The first set of scores are: M, H, M, L, H
The second set of scores are: 64, 57, 73, 62, 71
The ranking should be: e, b, c, a, d
Thanks for your help...