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 :)