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 ...
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.