Rank data based on 2 conditions

I recently came across an interesting question to find ranks based on 2 conditions. Let me explain the case and solution..

Assume this data!

Ranking Conditions

We are supposed to rank this data based on the following creteria

• A player gets a higher rank, if his score is higher than others
• In case there is a tie in the score then check the weight of the player. The lower weighted player gets a higher rank
• If there is a tie between score and the weight, both players get the same rank and the next rank is skipped!

For Eg. There is tie in the score for player B, C & D

• Since B has the least weight he gets rank 1
• C & D also have a tie in weight get the same rank i.e 2
• The next rank is skipped

Here is my solution..

Quick Explanation

• The Rank function calculates the rank based on the score
• The Sumproduct checks for 2 conditions
  • Are there any ties in the scores in the entire range ?
  • How many players who met the first condition have weight more than the current player (row) ?

DOWNLOAD THE SOLVED FILE FROM BELOW – it also includes the PowerQuery solution

How would you solve it?

I am curious to know how would you solve this and even more curious if you could do this via powerquery

More Challenges

1. Allot the correct shift
2. Find the Total Hours Challenge
3. Find the shift timing
4. Transpose rows into separate columns Challenge