Search code examples
excelsortingrangeranking

MS Excel - Sort and Rank Multiple Scores/Numbers in Descending Order

I am looking for a MS Excel Ranking Formula solution, without the use of VBA/Macros or MS Excel's built-in sort function, that will allow me to sort multiple columns in Descending Order containing Ranks (i.e., RANK 1, RANK 2, and RANK 3) against a Primary Field (i.e., PLAYER) and re-sort list in Descending order.

SAMPLE: Triple Ranking And Tie Breaking (Unsorted Ranking)

enter image description here

RESULTS: Sorted Ranking in Adjacent Columns in Descending Order: Sort by Column G (RANK 1), then by, Column H (RANK 2) and then by Column I (Rank 3) with matching/corresponding Player Name in Column J (PLAYER).

enter image description here

Solution

  • It's probably easier to do this with the sort, but here's what you can do:

    Make a new column next to player and call it "Overall Rank"

    =RANK(A2,A$2:A$26)+SUMPRODUCT(--(A2=A$2:A$26),--(B2<B$2:B$26))+SUMPRODUCT(--(A2=A$2:A$26),--(B2=B$2:B$26),--(C2<C$2:C$26))

    That will give you an overall rank, the resorting part is trivial, you will do an index match for each column.

    Column G would be: =INDEX(A$2:A$26,MATCH(ROW()-1,E$2:E$26))

    Column E is where the overall ranking formula would go.

    Explanation of the ranking formula:

    RANK() is pretty obvious, that's the rank on rank1.

    The first SUMPRODUCT adds up all players that have the same rank1 and a higher rank2. -- turns TRUE,FALSE into 1,0.

    The second SUMPRODUCT adds up all players that have the same rank1, the same rank2, and a higher rank3.