0

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?

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
new
  • 3
  • 2

2 Answers2

3

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)
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • If you have two or more identical Size value of the same Category this formula gives an incorrect result. If you take it into consideration will works. – Black cat Jul 29 '23 at 14:25
0

Try COUNTIFS().

 =COUNTIFS($B$2:$B2,$B2)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • this only works with sorted lists, it breaks if you change the 12 into a 8 for example – new Jul 29 '23 at 08:28