arraysgoogle-sheetsarray-formulasrank

Rank data by row across large dataset array in Google Sheets


I have a large array of data where I need to rank the values across each row, and I'm getting stumped. For a given row I can do a simple rank calculation (and expand it across that row with an arrayformula), but what I can't seem to figure out is how to expand this automatically down the array without pulling that formula down to each row.

So, for example for this dataset:

A B C D
$5000 $8000 $2000 $3000
$10000 $3000 $20000 $4000
$7,000 $3,000 $18,000 $1,000

I can rank the first row with =ARRAYFORMULA(rank(A1:D1,$A$1:$D$1)), which gives me: |A|B|C|D| |:----|:----|:----|:----| |2|1|4|3|

However, I'd like the formula to automatically extend down (I have hundreds of rows, and new data gets added regularly) to get the rank across each row like this:

A B C D
2 1 4 3
2 4 1 3
2 3 1 4

I've tried byrow and various other functions, but just can't seem to get it to expand properly. Anyone have any easy ideas?

Thanks in advance for all inputs and help :)


Solution

  • You may try:

    =byrow(A2:D,lambda(Σ,if(counta(Σ)=0,,map(Σ,lambda(Λ,rank(Λ,Σ))))))
    

    enter image description here