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

CodePudding user response：

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.