Forum Discussion
Help creating weighted average ranking
Hello,
I have multiple lists of rankings of certain people and would like to create a weighted average "ranking" for each individual using those lists. I was able to do it with multiple SUMIF() and COUNTIF() functions but have to manually add in each new column as another function to the equation, and I have over 50 of these lists. Is there any way to do this a bit more elegantly? Current formula is:
=(SUMIF($B$2:$B$11,G2,$A$2:$A$11)+SUMIF($C$2:$C$11,G2,$A$2:$A$11)+SUMIF($D$2:$D$11,G2,$A$2:$A$11))/(COUNTIF($B$2:$B$11,G2)+COUNTIF($C$2:$C$11,G2)+COUNTIF($D$2:$D$11,G2))
where G2 is the name of a person
2 Replies
There are couple of ways. Here is fully dynamic formula-
=LET(fx,CHOOSECOLS,arr,DROP(REDUCE("",TOCOL(B.:.F&"|"&A.:.A),LAMBDA(a,x,VSTACK(a,TEXTSPLIT(x,"|")))),1), GROUPBY(fx(arr,1),--fx(arr,2),AVERAGE,,0))Few semi dynamic and manual formulas-
=MAP(G2:G7,LAMBDA(X,SUMPRODUCT($A$2:$A$11,BYROW(--($B$2:$D$11=X),MAX))/COUNTIFS($B$2:$D$11,X))) =SUMPRODUCT($A$2:$A$11,BYROW(--($B$2:$D$11=G2),MAX))/COUNTIFS($B$2:$D$11,G2)Download the attached file.
=SUM(MMULT(TRANSPOSE($A$2:$A$11),N($B$2:$D$11=G2)))/COUNTIFS($B$2:$D$11,G2)
This works in my sample sheet. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021 or Excel 2024.
