CODEWITHSUNDEEP
excelexcel-formula
Эдуард Мартыненко
Эдуард Мартыненко

Reputation: 329

How do array formulas work with rank? ranking with duplicates without gaps, in order

How do array formulas work with the ranking? Ranking with duplicates without gaps, in order.

I have a task that needs to rank departments in order (by evaluating several indicators). But sometimes there is a situation when several departments get the same rank (duplicate rank). And a simple RANK() formula doesn't work properly, because of gaps in ranks (if two departments get the first place, the next one will be on the third position ).

I have found a solution to this little predicament - https://www.extendoffice.com/documents/excel/4283-excel-rank-without-skipping-numbers.html

But I can't wrap my head around how does this formula work exactly...

How does it work? 

 {=SUM(IF(A2>$A$2:$A$14,1/COUNTIF($A$2:$A$14,$A$2:$A$14)))+1}

Upvotes: 1

Views: 1358

Answers (1)

EttoreP
EttoreP

Reputation: 414

Not very intuitive, as all array formulas in fact. The explanation assumes you know what is an array formula, an array of values, and how they work.
If you disassemble the formula in more parts maybe it's simpler to understand:

  1. COUNTIF($A$2:$A$14,$A$2:$A$14) -> creates ad array of the same dimension of the range (A2:A14) and for each row counts how many times finds the value of the row, so if you have two departements with the same indicator (specifyed in column A in this case), in both the rows of the two departements the formula gives you 2, if you have three departements with the same indicator the formula gives 3 for each row, and so on.
  2. 1/pt.1 -> an array with the reciprocal of the count described in pt.1. Now if you sum all the n occurencies of the rows with the same indicator of this array you get 1, and here you have the trick. When (after) we will sum the number of indicators graters than the one in the row we are evaluating, this trik lets us to count 1 for each indicator instead counting n if there are more indicators with the same value.
  3. IF(A2>$A$2:$A$14,*pt.2*) -> returns an arry of the values of pt.2 "filtered" only with the values that correspond to rows where A2>An, so the ones where the indicator you are evaluating is grater than the one in the row you are in.
  4. SUM(*pt.3*) + 1 -> sums the values of the filtered array of pt. 3. Note that if you have more than one row with the same indicator, for the trik of pt. 2, the sum of them weights as 1 and not n.

Note that the A2>$A$2:$A$14 will number the rows in increasing order from the smaller value of the indicator to the bigger, if you prefer the opposite you can use smaller instead grater in the formula: A2<$A$2:$A$14

Upvotes: 1

Related Questions