SOLVED

Finding the highest selected choice

Copper Contributor

Greetings,

Feedback will be triaged by consensus between 6 people. Each one can select as High(H), or Med (M), or Low (L) priority. I need to find out which priority level is selected the highest? 

 

If 4 people has selected H, then the result should be H. If there are 2 selection for H, 2 selection for M and 2 selection for L selection then result should be HML. If there are 3 selection for H and 3 selection for L then result should be HL. 

 

Any advice or tips on how to do this and what function I should be using?  Thanks

 

Naren007_0-1709306544600.png

 

3 Replies

@Naren007 If you have Excel for MS365, try the following:

 

=LET(
    arr, HSTACK("H","M","L"),
    qty, COUNTIF(G2:L2, arr),
    CONCAT(TOROW(IFS(qty=MAX(qty), arr), 2))
)

 

Or, to spill the results for the entire dataset, use the BYROW function:

 

=LET(
    arr, HSTACK("H","M","L"),
    BYROW(G2:L4, LAMBDA(r, LET(
        qty, COUNTIF(r, arr),
        CONCAT(TOROW(IFS(qty=MAX(qty), arr), 2))))
    )
)

 

MAX Priority ResultsMAX Priority Results

 

Please see attached...

best response confirmed by Naren007 (Copper Contributor)
Solution

@Naren007 

365 solution:

=LET(
    GetResults, LAMBDA(row, LET(most_votes, MODE.MULT(CODE(row)), CONCAT(CHAR(most_votes)))),
    BYROW(vMatrix, GetResults)
)

Thx for the quick response and function works as intended. 

1 best response

Accepted Solutions
best response confirmed by Naren007 (Copper Contributor)
Solution

@Naren007 

365 solution:

=LET(
    GetResults, LAMBDA(row, LET(most_votes, MODE.MULT(CODE(row)), CONCAT(CHAR(most_votes)))),
    BYROW(vMatrix, GetResults)
)

View solution in original post