Compute subranks in spreadsheet column in combination with ArrayFormula (Google Sheets)

Question

I'm trying to find the inverse rank within categories using an ArrayFormula. Let's suppose a sheet containing

A    B         C
----------     -----
1    0.14      2
1    0.26      3
1    0.12      1
2    0.62      2
2    0.43      1
2    0.99      3

Columns A:B are input data, with an unknown number of useful rows filled-in manually. A is the classifier categories, B is the actual measurements.

Column C is the inverse ranking of B values, grouped by A. This can be computed for a single cell, and copied to the rest, with e.g.:

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

However, if I try to use ArrayFormula:

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

It only computes one row, instead of filling all the data range.

A solution using COUNT(FILTER(...)) instead of COUNTIFS fails likewise.

I want to avoid copy/pasting the formula since the rows may grow in the future and forgetting to copy again could cause obscure miscalculations. Hence I would be glad for help with a solution using ArrayFormula.

Thanks.

I think sumifs and countifs just don't work with arrays in the way you want them to - there is probably another way to do it but less obvious. — Tom Sharpe
Hi Tom, the problem is in the grouping. An overall rank using a single countif does work with ArrayFormula, e.g.: =arrayformula(1+countif(B$2:B, "<" & B$2:B)) — Álex
Agree - it seems that Google just haven't implemented it yet with countifs / sumifs. Plz see my answer below, but obviously a much more roundabout way of doing it. — Tom Sharpe

Unknown Unknown · Accepted Answer · 2017-11-30T17:08:30

I don't see a solution with array formulas available in Sheets. Here is an array solution with a custom function, =inverserank(A:B). The function, given below, should be entered in Script Editor (Tools > Script Editor). See Custom Functions in Google Sheets.

function inverserank(arr) {
  arr = arr.filter(function(r) {
    return r[0] != "";
  });
  return arr.map(function(r1) {
    return arr.reduce(function(rank, r2) {
      return rank += (r2[0] == r1[0] && r2[1] < r1[1]);
    }, 1);
  });
}

Explanation: the double array of values in A:B is

filtered, to get rid of empty rows (where A entry is blank)
mapped, by the function that takes every row r1 and then
reduces the array, counting each row (r2) only if it has the same category and smaller value than r1. It returns the count plus 1, so the smallest element gets rank 1.

No tie-breaking is implemented: for example, if there are two smallest elements, they both get rank 1, and there is no rank 2; the next smallest element gets rank 3.

Compute subranks in spreadsheet column in combination with ArrayFormula (Google Sheets)

3 Answers