Ranking 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 Data Based on 2 Conditions 2

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
Ranking Data Based on 2 Conditions 3

  • 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..

Ranking Data Based on 2 Conditions 4

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

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI