Ranking with Excel for a Team based on different criteria

[CANDICE48]

Hi, Please can someone assist, I am trying to create a ranking based on multiple criteria. Each section has been ranked now I would like to see who is leading in terms of a "FINAL RANK" based on all the ranks. please can someone assist me? Please can someone assist with a formula

[TABLE="width: 584"]
<tbody>[TR]
[TD][/TD]
[TD]MBR[/TD]
[TD]Rank[/TD]
[TD]NPI[/TD]
[TD]Rank[/TD]
[TD][/TD]
[TD]CORR[/TD]
[TD]Rank[/TD]
[TD][/TD]
[TD]FINAL RANK[/TD]
[/TR]
[TR]
[TD]NG[/TD]
[TD]9.83[/TD]
[TD]1[/TD]
[TD="align: right"]14.63[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]233[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]MW[/TD]
[TD]9.78[/TD]
[TD]2[/TD]
[TD="align: right"]9.67[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]237[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]LS[/TD]
[TD]9.35[/TD]
[TD]4[/TD]
[TD="align: right"]4.64[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]450[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]RB[/TD]
[TD]9.40[/TD]
[TD]3[/TD]
[TD="align: right"]7.73[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]296[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]CN[/TD]
[TD]9.30[/TD]
[TD]5[/TD]
[TD="align: right"]6.43[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]279[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]JP[/TD]
[TD]8.75[/TD]
[TD]6[/TD]
[TD="align: right"]10.91[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]190[/TD]
[TD]9[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD]8.42[/TD]
[TD]9[/TD]
[TD="align: right"]6.96[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]417[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]MM[/TD]
[TD]8.62[/TD]
[TD]8[/TD]
[TD="align: right"]8.03[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]506[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]SM[/TD]
[TD]8.67[/TD]
[TD]7[/TD]
[TD="align: right"]13.86[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]288[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 436357]

How about this formula in the first question mark cell then drag down:

=AVERAGE(D2,F2,I2)

 

[CANDICE48]

Hi, thank you for this, but its giving me duplicate rankings : Please help, I want it to rank from 1-9, with one being the best:

[TABLE="width: 584"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]MBR[/TD]
[TD]Rank[/TD]
[TD]NPI[/TD]
[TD]Rank[/TD]
[TD][/TD]
[TD]CORR[/TD]
[TD]Rank[/TD]
[TD][/TD]
[TD]FINAL RANK[/TD]
[/TR]
[TR]
[TD]NG[/TD]
[TD]9.83[/TD]
[TD]1[/TD]
[TD="align: right"]14.63[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]233[/TD]
[TD]8[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]MW[/TD]
[TD]9.78[/TD]
[TD]2[/TD]
[TD="align: right"]9.67[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]237[/TD]
[TD]7[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]LS[/TD]
[TD]9.35[/TD]
[TD]4[/TD]
[TD="align: right"]4.64[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]450[/TD]
[TD]2[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]RB[/TD]
[TD]9.40[/TD]
[TD]3[/TD]
[TD="align: right"]7.73[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]296[/TD]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]CN[/TD]
[TD]9.30[/TD]
[TD]5[/TD]
[TD="align: right"]6.43[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]279[/TD]
[TD]6[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]JP[/TD]
[TD]8.75[/TD]
[TD]6[/TD]
[TD="align: right"]10.91[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]190[/TD]
[TD]9[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD]8.42[/TD]
[TD]9[/TD]
[TD="align: right"]6.96[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]417[/TD]
[TD]3[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]MM[/TD]
[TD]8.62[/TD]
[TD]8[/TD]
[TD="align: right"]8.03[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]506[/TD]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]SM[/TD]
[TD]8.67[/TD]
[TD]7[/TD]
[TD="align: right"]13.86[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]288[/TD]
[TD]5[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 436357]

In order to do that maybe there needs to be more weight for one of the rankings.

 

[bobsan42]

It is actually up to you to DECIDE how to calculate the Final Rank.
Normally, when an evaluation is made based on several different criteria some of them will have more weight than others.
So each criteria will have a coefficient. Normally the sum of all coefficients will be 1. (coeff1+coeff2+coeff3+...=1) (or you can do it as 100%)
So for each line you can get a total score like: Score1*coeff1+score2*coeff2 + score3*coeff3 + .... = Total score
Then you can rank the total score.
At least this is how I would do it. The coefficients are arbitrary, depend on the topic and are usually a matter of personal choice).

P.S.: It is not impossible to have same ranks. And it happens all the time - one thing compensates for another.

 

[Legacy 436357]

I will check back soon but something like this:

Weighted result

=B2*0.25+C2*0.3+D2*0.4+E2*0.05

=B3*0.25+C3*0.3+D3*0.4+E3*0.05

=B4*0.25+C4*0.3+D4*0.4+E4*0.05

=B5*0.25+C5*0.3+D5*0.4+E5*0.05 Rank =RANK(F2,$F$2:$F$5) =RANK(F3,$F$2:$F$5) =RANK(F4,$F$2:$F$5) =RANK(F5,$F$2:$F$5) <tbody> </tbody>

<tbody>
</tbody>

 

[Legacy 436357]

Okay I put this together with percentage of weight for each category. You can alter the percentages for your needs:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1		MBR	Rank	NPI	Rank	CORR	Rank	Weighted Result	FINAL RANK
2		50%		30%		20%
3	NG	9.83	1	14.63	1	233	8	55.904	7.00
4	MW	9.78	2	9.67	4	237	7	55.191	8.00
5	LS	9.35	4	4.64	9	450	2	96.067	2.00
6	RB	9.4	3	7.73	6	296	4	66.219	4.00
7	CN	9.3	5	6.43	8	279	6	62.379	6.00
8	JP	8.75	6	10.91	3	190	9	45.648	9.00
9	TM	8.42	9	6.96	7	417	3	89.698	3.00
10	MM	8.62	8	8.03	5	506	1	107.919	1.00
11	SM	8.67	7	13.86	2	288	5	66.093	5.00
Sheet1