Ranking Based on Multiple Criteria

[khannadh]

Hi guys,

I need to rank performance of people based on 4 attributes.
Each person should be ranked on a 1 - 5 scale.

- I need to further split this into 2 cases and check which approach is more appropriate:

- Case 1:
- For attributes 1 to 3, the higher the value the better. For attribute 4, the lower the value the better
- All attributes are important

Name Attribute 1 Attribute 2 Attribute 3 Attribute 4
john 80 80 90 2
jake 50 70 80 4
jill 90 60 80 5
jim 100 85 60 2
jade 50 80 50 7
jin 30 90 50 6

- In this case, since all attributes are important, john should have rank 1 since he is in the top range of the first three attributes and the bottom range of the fourth attribute.
- Basically, in this case, the rank on a 1-5 scale would depend on the combination of all attributes.

Case 2:
Same data

- For attributes 1 to 3, the higher the value the better. For attribute 4, the lower the value the better (same as above)
- Here, attribute 1 gets 50% of the weight, attribute 2 gets 15%, attribute 3 gets 15% and attribute 4 gets 20%.

Any help is appreciated.
Thank You

[quekbc]

You need a way to "normalise" them, and then combine them.

Based on what you have, I think, to normalise them, you can reclassify the 4th attribute to be
1) change from lower value = better, to higher value = better. You can do this by taking the Upper Limit less the current attribute. Looking at the list you posted, I'm going to assume that attribute ranges from 0 to 10. As such, your table can be represented as 10 - X, which then becomes, the larger, the better.

Row\Col	A	B	C	D	E	F
1	Name	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Normalise At4
2	john				2	8
3	jake				4	6
4	jill				5	5

2) Secondly, is to arrange the scale. That is, I'm assuming attribute 1 to 4 is from 0-100. We'll leave this as it is, but would reclassify Attribute 4 such that it's between 0-100. So, in other words, multiply them by 10. (so the numbers ranges from ~0-100)

Row\Col	A	B	C	D	E	F
1	Name	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Normalise At4
2	john				2	80
3	jake				4	60
4	jill				5	50

You can then give weights to them, and combine them up using a SUMPRODUCT

Row\Col	A	B	C	D	E	F	G
1	Name	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Normalise At4	Combined
2	Weights	25%	25%	25%		25%
3	john	80	80	90		80	82.5
4	jake	50	70	80		60	65
5	jill	90	60	80		50	70

Formula in G3 = =SUMPRODUCT($B$2:$F$2,B3:F3)

Once you have that "Combined" list, you can use the RANK function to rank them up.

You can change the weights to solve for Case 2.

[khannadh]

Hi Quekbc.

This is great.

Let me be more specific:

I have some attributes with different scales and ranges.
Please see below:

Attribute 1 Range: -100% to 12214% (higher the better)
Attribute 2 Range: 0% to 100% (higher the better)
Attribute 3 Range: 0% to 100% (higher the better)
Attribute 4 Range: 0 to 178 (higher the better)
Attribute 5 Range: 0 to 18 (lower the better)

I need to create a scorecard and rank people based on the above attributes. Every attribute is important in creating a ranking.
How would normalization/standardization and scaling work here? And finally how would ranking be established?

I can attach a spreadsheet, but I cant figure out how.

Any help is appreciated.

Thank You

[quekbc]

Apologies, completely lost track of this post.

So, to normalise/standardise the attributes you mentioned above, for those higher the better type attributes, you can do,
(Actual Score - Lower Limit) / (Upper Limit - Lower Limit)

To convert lower the better to the opposite then do
Lower Limit + (Upper Limit - Actual Score)
Which you can normalise using
(Lower Limit + (Upper Limit - Actual Score) - Lower Limit) / (Upper Limit - Lower Limit)

The ranking can then be combined in a similar manner as what I mentioned above, as long as they add up to 100%

Or see attached.

NOTE: To attach a file, click on Go Advanced (bottom right of the reply window) - then click on the paperclip sign on the top of the reply window or Manage Attachments at the bottom of the "Advanced" website.

[newdoverman]

A few comments:
1. Don't mix % values with actual values for the first attributes. Pick either all % or all actual values then apply any "weighting".
2. The 4th attribute lower is better, how much better is the question here. If you have a table that translates the values in ascending order with their corresponding values in descending order the problem is easy as you would simply apply the weighting to each Attribute then add them together. (weighting amounts total 100%)

All of the following use the name of the Attribute instead of actual values just to show what is going on.

Please Login or Register  to view this content.

Attribute 4 (lower is better) could be determined with something like this:

Please Login or Register  to view this content.

Then the weighted formula if all combined would look like this:

Please Login or Register  to view this content.

Using your example values this is how the formula would be using cell references. Enter in F2 and fill down:

Please Login or Register  to view this content.

To rank the values in column F:

Please Login or Register  to view this content.