Forum Discussion
Roman0919
Mar 14, 2020Copper Contributor
Help!!! how can I rank or score this???
Looking at the 3 groups below. I asked each group to give me their list of top 5. Each group was asked to rank 1-5 with 1 being the best and 5 being the least. how can I score these so the 1's ar...
Patrick2788
Mar 14, 2020Silver Contributor
- SHIKHA1010Jul 19, 2024Copper Contributor
Patrick2788 I am using your formula provided for the above solution but it's showing an error. Can u write the syntax for the same? I made an excel file with same value but its showing error.
- Patrick2788Jul 19, 2024Silver Contributor
Much has changed since 2020.
A 2024 solution might look like this:
=LET( swapped, SWITCH(scores, 5, 1, 4, 2, 3, 3, 2, 4, 1, 5, 0), total, LAMBDA(row, SUM(row)), BYROW(swapped, total) )
Start by swapping out the scores - 5 becomes 1, 4 becomes 2, etc. Next, use BYROW to summarize the matrix.
With ETA and Insider, the solution becomes even shorter:
=LET( swapped, SWITCH(scores, 5, 1, 4, 2, 3, 3, 2, 4, 1, 5, 0), BYROW(swapped, SUM) )