I recently came across an interesting question to find ranks based on 2 conditions. Let me explain the case and solution..
Assume this data!
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..
- 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
- Allot the correct shift
- Find the Total Hours Challenge
- Find the shift timing
- Transpose rows into separate columns Challenge