Forum Discussion

Naren007's avatar
Naren007
Copper Contributor
Mar 01, 2024

Finding the highest selected choice

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

 

 

Excel
Formulas and Functions
  • djclements's avatar
    djclements
    Bronze Contributor
    Mar 01, 2024

    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 Results

     

    Please see attached...

    • Naren007's avatar
      Naren007
      Copper Contributor
      Mar 01, 2024

      Thx for the quick response and function works as intended. 

Resources