Ranking with multiple criteria and assigning a weight

[thisisbigbear]

Good morning,

This is my first time posting on here so if I make a mistake I want to go ahead and apologize. I have am attempting to rank out individuals based on 4 different categories.

QC: goal of 90%; the higher the better; 65% weight
ADH: goal of 95%; the higher the better; 15% weight
ACW: goal of 20 seconds; the lower the better; 15% weight
ABS: goal of 0%; the lower the better; 5% weight

I am going to try and attach a worksheet with some sample data. Any help would be greatly appreciated.

[CK76]

I'd use 2 steps.

1. Add Weighted score column.
In F2: Copy Down

Please Login or Register  to view this content.

2. Rank column.
In G2: Copy Down

Please Login or Register  to view this content.

Edit: Oh, wait, I didn't read the part about lower is better for ACW & ABS. Disregard above.

[CK76]

One question.
What is the scoring matrix for ACW?
I.E. Is there score/% value assigned to 23, 22, 21, 20.... sec? Is Anything 20 sec or below given same score?

[davsth]

Its not clear for example
A 100% 100% 21 0
B 90% 95% 20 0

which is the best? A fails narrowly on 1 criteria, but is better on the other critieria?

you can look at the difference from the target and multiply this by the waiting. the concern is the times as you could take a long time and this would dwarf everything

[CK76]

Yep. Typically speaking you'd use score matrix for something like this.

Ex:

ACW	Score
<=20	100
21	95
22	90
23	85
24	80
25	75
26	70
27	65
>=28	0

[thisisbigbear]

My original information was a little confusing now that I read it. Sorry about that. I listed goals for each criteria, but that isn't important when it comes to the ranking and weighting because regardless of what my work considers pass/fail, the lower the ACW the better. Same goes for the other categories.

I think I figured it out though. I came across another thread where user quekbc provided the following formulas:

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)

I tried these out and they worked great for my data. Thank you both for the quick responses though. I will definitely be coming back here in the future. Cheers.