Search code examples
excelexcel-formularanking

Excel: Ranking scores with ties

I am looking how to pick a winner, a 2nd and 3rd place out of a list of scores (player names in row 1 and scores in row 2). The difficulty is how to take into account when there are ties. The rules I would like to follow are the following:

  1. If only 1 player has a score and all others have 0 then 2nd place and 3rd place should show "-".
  2. If two players have the same score, then take the one that is closest to the left hand side (towards column A)

Sofar I have the following:

The winner:

=INDEX($A$1:$G$1;MATCH(LARGE($A2:$G2;1);$A2:$G2;0))

2nd place:

=IF(COUNTIF(A2:G2;">"&0)=1;"-";INDEX($A$1:$G$1;MATCH(LARGE($A2:$G2;2);$A2:$G2;0)))

Example: Player 1 (250), Player 2 (300), Player 3 (300) => Winner Player 2, 2nd place Player 3

Thanks a lot,

Solution

  • Following is the formula for finding second place in case two players has same highest score -

    =IF(COUNTIF(A2:H2,">"&0)=1,"-",IF(LARGE($A2:$G2,1)=LARGE($A2:$G2,2),INDEX($A$1:$G$1,MATCH(LARGE($A2:$G2,2),OFFSET(A2,0,MATCH(LARGE($A2:$G2,1),$A2:$G2,0),1,6),0)+MATCH(LARGE($A2:$G2,1),$A2:$G2,0)),INDEX($A$1:$G$1,MATCH(LARGE($A2:$G2,2),A2:G2,0))))

    Basically, I have added one if formula around your given formula -

    1) If two players has same highest score -

    LARGE($A2:$G2,1)=LARGE($A2:$G2,2)

    change the range of match formula lookup array. This dynamic range is specified using offset -

    INDEX($A$1:$G$1;MATCH(LARGE($A2:$G2;2);OFFSET(A2;0;MATCH(LARGE($A2:$G2;1);$A2:$G2;0);1;6);0)+MATCH(LARGE($A2:$G2;1);$A2:$G2;0))

    2) If winner and second place player does not have same score, original formula is used.

    INDEX($A$1:$G$1;MATCH(LARGE($A2:$G2;2);A2:G2;0))

    Limitation of this approach is that it would not be possible to make this formula generic.

    For example, for finding third place, there are 4 possible scenarios -

    1. Check whether winner, second place and third place has same score, then adjust match formula lookup array to start after second place score column
    2. If winner and third place has same score, then calculate match formula lookup array to start after winner score column.
    3. If second and third place has same score, then calculate match formula lookup array to start after second place score column.
    4. Neither score of winner nor second nor third place is same, then original formula will be used.