0

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

enter image description here

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?

Thanks a lot in advance.

2
  • 1
    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.]
    – Pesetas74
    Jul 17 at 19:43

1 Answer 1

1

This is what you are looking for:

enter image description here

: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.
  • Benchmarks used for grading are adjustable.

Formula in cell:

  • 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"))

N.B. Adjust cell references in formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.