# Excel: calculating "weighted ranking"

I have the following data set with a list of players and their contribution to the group they belong.

I've calculated the % of the player contribution within his/her group in "% Player Contribution Within Group":

``````=[@[Player Contribution]]/SUMIF([Group];[@Group];[Player Contribution])
``````

Then I've calculated the ranking of that % contribution in "Player Contribution's Rank Within Group"

``````=COUNTIFS([Group];[@Group];[% Player Contribution Within Group];">"&D2)+1
``````

Since the % contribution's may vary a lot between players (is very evident for Group B), to show this difference (for instance) between the first player of Group B (84,97%) and the second and third (less than 8%) of the same group, I would like to calculate a sort of "weighted ranking" (it it makes sense .. not quite sure). Is there a way (a formula) to express with the result of a calculation this kind of difference?

• What are you trying to achieve qualitatively? The ranking is independent of the weighting, because regardless of the fact that the value for player 8 is much larger, player 8 would also be in 1st place if there is only a difference of one margin. At the same time, the percentage already reflects your weighting over all players in a given group.
– karl
Jul 17 at 18:10
• Well, now I feel really foolish after reading your comment :) What I wanted to achieve was basically "grading" (the amount of "contribution" above is just one of the aspects) the importance of a player in comparison with the others of the same group. I wanted to assign to each contributor a label "Top", "Average", "Poor" or "No Contributor" based on his/her relative importance to the group he/she belongs. Guess I got lost overthinking it and should just decide which % range assign to each label [100 to 50 = top, etc.] Jul 17 at 19:43

This is what you are looking for:

:Caveat:

• For better understanding and good results I've calculated Overall & Rank in group.
• Formula used for overall Rank avoids duplicates & is in Ascending order.
• G2: `=F2*100/SUMPRODUCT((\$E\$2:\$E\$11=E2)*(\$F\$2:\$F\$11))`
• H2: `=RANK(G2,\$G\$2:\$G\$11,0)+COUNTIF(\$G\$2:G2,G2)-1`
• I2: `=SUMPRODUCT((\$E\$2:\$F\$11=E2)*(F2<\$F\$2:\$F\$11))+1`
• J2: `=IF(G2>60,"Very Good",IF(G2>50,"Good","Bad"))`