CODEWITHSUNDEEP
google-sheetsarray-formulas
Álex
Álex

Reputation: 1703

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

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.

Upvotes: 1

Views: 417

Answers (3)

Max Makhrov
Max Makhrov

Reputation: 18717

My version of an array formula, it works when column A contains text:

=ARRAYFORMULA(RANK(ARRAY_CONSTRAIN(VLOOKUP(A1:A,{UNIQUE(FILTER(A1:A,A1:A<>"")),ROW(INDIRECT("a1:a"&COUNTUNIQUE(A1:A)))},2,)*1000+B1:B,COUNTA(A1:A),1),ARRAY_CONSTRAIN(VLOOKUP(A1:A,{UNIQUE(FILTER(A1:A,A1:A<>"")),ROW(INDIRECT("a1:a"&COUNTUNIQUE(A1:A)))},2,)*1000+B1:B,COUNTA(A1:A),1),1) - COUNTIF(A1:A,"<"&OFFSET(A1,,,COUNTA(A1:A))))

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34230

Well this does give an answer, but I had to go through a fairly complicated manoeuvre to find it:

=ArrayFormula(iferror(VLOOKUP(row(A2:A),{sort({row(A2:A),A2:B},2,1,3,1),row(A2:A)},4,false)-rank(A2:A,A2:A,true),""))

![enter image description here

So

  1. Sort cols A and B with their row numbers.
  2. Use a lookup to find where those sorted row numbers now are: their position gives the rank of that row in the original data plus 1 (3,4,2,6,5,7).
  3. Return the new row number.
  4. Subtract the rank obtained just by ranking on column A (1,1,1,4,4,4) to get the rank within each group.

In the particular case where the classifiers (col A) are whole numbers and the measurements (col B) are fractions, you could just add the two columns and use rank:

=ArrayFormula(iferror(rank(A2:A+B2:B,if(A2:A<>"",A2:A+B2:B),true)-rank(A2:A,A2:A,true)+1,""))

Upvotes: 0

user6655984
user6655984

Reputation:

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

  1. filtered, to get rid of empty rows (where A entry is blank)
  2. mapped, by the function that takes every row r1 and then
  3. 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.

Upvotes: 1

Related Questions