Search code examples
excelconditional-statementsrankingrank

Excel creating multiply conditions ranking

I am trying to make a conditional ranking in this chart:

enter image description here On the right you can see the total score and ranking from the Match 1,2,3 (sum) on line A and C I have a tie score (3 points and both ranked at 2nd place), I'd like to to add a 2nd and 3rd conditions to determinate the final ranking. If there is a tie in score (both are in same ranking), then to determine who is 2nd and who is 3rd place I want to use the values from the XX's column first. The higher value will win the 2nd place and in a case there is still a tie (as both are the same value of 0), it will continue and check the value on the X's column and since line C = 27 and line A = 21, line C will be ranked as 2nd place and line A will be ranked as 3rd place. Also will like to add an option to continue with 3 condition if there is still a tie.

I am using a simple ranks formula =RANK(AK7,$AK$7:$AK$10) to determine the rank.

Solution

  • Very simple 'math' solution: do a linear combination with weights. Do something like

    Wscore = 1000*Score + 10*XXvalue + Xvalue

    then sort values using simple rank function.

    Just change weights according to your values distribution, if needed.