Normalizing/Standardizing data to be used for ranking

[deekay]

Hi guys,

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 have attached a spreadsheet to put things into context.

Any help is appreciated.

Thank You

 

[Chihiro]

First of all you want to decide on Weight Distribution. If one attribute should be weighted heavier than other or not. Distribution should equal 1, or 100%. If all should be equal in importance, then each attribute will be 20% of final score.

Then, you want to decide on matrix for each attribute.
Say for Attribute 2 for example. Should 100~95% be 100 mark or each percentage point equating to 1 mark?
For Attribute 5, 0 = 100, 1 = ? etc.
This part will dictate how difficult your final set up is going to be.

After you have decided on above, you can set up table in another sheet listing out matrix for each attribute.

You will then do INDEX,MATCH or other variations to retrieve matching score from the table to each attribute and multiply by appropriate weight.

Add the result up and you can rank based on resulting score.

Hope that helps.