Help!!! how can I rank or score this???

[Roman0919]

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 are worth more than the 5's (5=1 and 1=5).  As you can see from the example below, the SUM function does not work. APPLE scored the highest with an 11 but in reality was scored the lowest (5) by two groups. On the other hand HORN scored a total of 5 but in realty was picked the highest a 1 by two of the groups.

any help would be much appreciated.

[#2]

@Roman0919 Well, if the lowest number represents the best score, you need to sort the list in ascending order. And I guess you need the average the scores as well. HORN has received 5 points from 3 groups. That's an average of 1.66667. Do the same for all others and HORN will turn out to be best (i.e. lowest average). APPLE and GREEN come out worst with 11/3 = 3.66667 . BANANA, HOUSE, FLOWER and FLAG will share 2nd place, each with 3 points.

If this is not what you had in mind, you should perhaps just turn around your scoring table. 1 is worst, 5 is best and the product with the highest overall score comes out as best.

[#3]

Hi @Roman0919

You can create a helper table giving the point score and lookup your values and replace the scores lowest to highest.

Formula reads as below: 

=SUMPRODUCT(IFERROR(VLOOKUP(B3:D3,Helper,2,FALSE),0))

Attached is the solution file for your reference.

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

[#4]

If you find the above solution resolved your query don't forget mark as Official Answer.

[#5]

@Roman0919 

Your rankings would be reversed by the formula

= IF( rank, 6 - rank, 0 )

One way of summing individual rows is to use MMULT

= MMULT( IF( rank, 6 - rank, 0 ), {1;1;1} )

Alternatively you could use a relative reference to a single row at a time, in which case the formula

= SUM( IF( nameRank, 6 - nameRank, 0 ) )

needs to be copied down.

[#6]

@Roman0919 

[#7]

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

[#8]

@SHIKHA1010 

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