CODEWITHSUNDEEP
google-sheetsspreadsheetrankingrank
Deacon
Deacon

Reputation: 23

Ranking by multiple criteria

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

Upvotes: 2

Views: 2149

Answers (1)

doubleunary
doubleunary

Reputation: 19249

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.

Upvotes: 1

Related Questions