Search code examples
excelexcel-formulaexcel-2016ranking-functions

EXCEL rank.eq function: rank numbers in a row in relation to other numbers of the same assigned category

MY Spreadsheet was made using Excel 2016. I have a table with different numbers with assigned categories:

Size Category
11 solid
12 liquid
11 liquid
7 liquid
9 solid

I want to return the rank of the numbers, but only rank them based on other numbers in the same category like this:

Size Category rank
11 solid 1
12 liquid 1
11 liquid 2
7 liquid 3
9 solid 2

My current solution that accomplishes this requires me to add two new rows for each category:

Size Category rank =IF(liquid rank<>"",liquid rank,IF(solid rank<>"",solid rank)) liquid size =IF(category="liquid",size,"") liquid rank =IF(liquid size="","",RANK.EQ(liquid size,liquid size)) solid size =IF(category="solid",size,"") solid rank =IF(solid size="","",RANK.EQ(solid size,solid size))
11 solid 1 11 1
12 liquid 1 12 1
11 liquid 2 11 2
7 liquid 3 7 3
9 solid 2 9 2

Is there a way to have all the calculations happening in the additional rows be run inside the RANK row and thus get rid of them? Or is there a more elegant solution as a whole?

Solution

  • You could maybe try:

    enter image description here

    Formula in C2:

    =COUNTIF(B2:B6,B2:B6)-COUNTIFS(A2:A6,"<"&A2:A6,B2:B6,B2:B6)

    I suppose the Excel-2016 variant to drag down would be:

    =COUNTIF(B$2:B$6,B2)-COUNTIFS(A$2:A$6,"<"&A2,B$2:B$6,B2)