Search code examples
excelrankingdense-rank

How can I get a rank by category with ranks that are successive even with duplicate values?

I have an array with employees that corresponds to different classes and that each have a score. I want to be able to rank these employees depending on their classes.

Right now I'm using D2=(SUMPRODUCT((B2=$B$2,$B$9)*(C2<$C$2:$C$9))+1), which allows me to get the ranks, but when a score appears twice the ranks are not successive.

For example in the MWE below I get the ranks 1, 2 and 4 for class A and ranks 1 and 3 for class B, while I'd like just the ranks 1, 2 and 3.

MWE

(the classes are sorted in this array for clarity but it won't be the case in my actual problem)

Thanks for your help

Solution

  • Here is the FREQUENCY version:

    =SUMPRODUCT((FREQUENCY(IF($B$2:$B$9=B2,$C$2:$C$9),$C$2:$C$9)>0)*($C$2:$C$10>=C2))

    This is an array formula and must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly Excel will put {} around the formula.

    Note that the range on the end is one more row than the others.

    My

    If one has access to the New Dynamic Array formula then this gets A LOT easier:

    =SUMPRODUCT(--(UNIQUE(FILTER($C$2:$C$9,$B$2:$B$9=B2))>=C2))

    enter image description here