User Profile
Harun24HR
Bronze Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: Help creating weighted average ranking
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.Re: Help! Removing a Space in Excel
I copy your data and paste to my Excel. I found that you have hidden character rather than space which represent Char(160). So, use SUBSTITUTE() function to remove those chars like- =SUBSTITUTE(A2,CHAR(160),"") If, in your real file you have only spaces (more than one) then use the following formula- =TEXTJOIN(" ",1,TEXTSPLIT(A3," "))
