Ranking by multiple criteria

user_17208985 0
Viewed 399

I'm trying to make a scoresheet for a competition and I faced a problem when it comes to ranking the competitors.

An example can be found here: https://docs.google.com/spreadsheets/d/1tSiHxGlgNo9oYMypXJ9_ZGrn8O_POQkHspLXhVL9k7M/edit?usp=sharing

The competitors should be ranked by these rules:

  1. Best attempt (higher wins), if this is a tie, then by
  2. Bodyweight (higher wins), if this is also a tie, then by
  3. Second best attempt (higher wins), if this is also a tie, then by
  4. Third best attempt (higher wins), if this is also a tie, then they share a rank
  5. If there no valid attempts (0 in the cell), no rank shall be given

I've been able to get to the 2nd rule (where it compares the bodyweights in case the best attempts are same) but beyond that I'm out of knowledge.

This is what I currently have in cell F2:

=IF(B2=0;"";RANK.EQ($B2; $B$2:$B$10) + COUNTIFS($B$2:$B$10; $B2; $C$2:$C$10; ">" &$C2))

rank spreadsheet ranking google-sheets
1 Answers

Use weights that differ by order of magnitude with each column, like this:

=arrayformula( 
  iferror( 
    rank( 
      B2:B * 1000000 + C2:C * 10000 + D2:D * 100 + E2:E; 
      B2:B * 1000000 + C2:C * 10000 + D2:D * 100 + E2:E 
    ) 
    / 
    sign(B2:B + D2:D + E2:E) 
  ) 
)

This array formula should go to row 2 of a free column. It will fill the whole column in one go.

user_130451930
Related