CODEWITHSUNDEEP
excelexcel-formula
C.C
C.C

Reputation: 179

Excel conditional ranking with duplicate values

I want to rank some data within their subgroups. Currently I have Rank as below by using:

=IF(B2<>"",COUNTIFS($A$2:$A$800,A2,$B$2:$B$800,"<"&B2)+1,"")

It skips blank, however, it does rank duplicate by skipping rank numbers.

How can I get the Desired Rank (1) and (2)? Please note there are bland cells and ranking should be related to SubGroup (i.e. to rank numbers in RED and rank numbers in BLUE separately by using only one formula). Thank you.

enter image description here

Upvotes: 0

Views: 4525

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Not sure why you don't want to use the RANK function.

I did and I used the following Defined Names:

SubGroup    =Sheet1!$A$2:$A$800
Values      =Sheet1!$B$2:$B$800

Since the table is sorted by SubGroup, I was able to use the OFFSET function with the height parameter to return only the relevant Values for each Subgroup.

RANK

=IF(B2="","",RANK(B2,OFFSET($A$1,MATCH(A2,SubGroup,0),1,COUNTIF(SubGroup,A2)),1))

UNIQUE RANK

=IF(B2="","",RANK(B2,OFFSET($A$1,MATCH(A2,SubGroup,0),1,COUNTIF(SubGroup,A2)),1)+COUNTIFS($A$2:A2,A2,$B$2:B2,B2)-1)

DENSE RANK

=IF(B2="","",SUMPRODUCT( (FREQUENCY(OFFSET($A$1,MATCH(A2,SubGroup,0),1,COUNTIF(SubGroup,A2)), OFFSET($A$1,MATCH(A2,SubGroup,0),1,COUNTIF(SubGroup,A2))) > 0) * (B2 >= OFFSET($A$1,MATCH(A2,SubGroup,0),1,1+COUNTIF(SubGroup,A2))) ))

Note that the final term in the DENSE RANK formula has one additional element than would be returned by the COUNTIF(SubGroup,A2) formula.

enter image description here

Upvotes: 1

Related Questions