Search code examples
excelexcel-formularank

Creating RANK in Excel with conditions in Excel


I have a file I need to have ranked but need to ignore specified rows.

Here is an example.

   A          B          C
INCLUDE     ORDER     RANK ORDER
  1           1           1
  0           2           
  0           6           
  1           7           7
  1           3           3
  0           5            
  1           4           4

I need to ignore blank spaces and start ranking from when the 1 appears in column A. The values start on Row 2.

Desired table:

   A           B              C
INCLUDE      ORDER        RANK ORDER
   1           1              1
   0           2               
   0           6       
   1           7              4
   1           3              2
   0           5             
   1           4              3

I have done =IF(A2 = 1,IF(COUNTIFS(A$2:A2,1,B$2:B2,) > RANK.EQ(B2,B$2:B2,0),RANK.EQ(B2,$B$2:$B$300,1)), " ") This will get the first table but I need the second table any suggestions?


Solution

  • So this seems to work:

    enter image description here

    Formula in C1:

    =IFNA(XMATCH(B2:B8,SORT(FILTER(B2:B8,A2:A8))),"")