Ranking Data

[pearsonerik]

Hello, any help would be appreciated!

I have a sheet with four different data sets which I would like to
rank, here is the problem. Each data set consists of two columns, one
column is an ID number and the other column is an associated numerical
score, these need to be linked. This is the same for each of the four
sets of two columns. Then, the numerical score for each data set needs
to be multiplied by a factor, creating another numerical value, linked
to the ID number. Then each of these numerical values, linked to their
ID number will be sumed to created an overall score for each ID number.
Finally I would like to rank the associated ID numbers with the
computed score. If this does not make sense, please email me at
(e-mail address removed)! Thanks for your time.

 

[Bernie Deitrick]

Erik,

You need to first make up a list of your unique ID numbers.

This solution assumes that each ID number appears only once in each
data set, and that all ID numbers appear in each data set.

Extract your scores using 4 separate VLOOKUPs, multiplying them by
their factors and adding them together, along the lines of this, with
your ID number in cell J1, and your data tables in columns A:B, C,
E:F, and G:H, from row 1 to 20:

=1.1*VLOOKUP(J1,A1:B20,2,FALSE) + 1.2*VLOOKUP(J1,C120,2,FALSE) +
1.3*VLOOKUP(J1,E1:F20,2,FALSE) + 1.4*VLOOKUP(J1,G1:H20,2,FALSE)

Copy that formula down, to match your ID numbers, then use the RANK
function in another column to return their rank.

HTH,
Bernie
MS Excel MVP