Google-spreadsheet formula to get number ranks in column

Question

I'm looking for formula/function for google-spreadsheet. Sorry for the title, but let me explain. I have a column with 5 rows of numbers:

2
5
9
7
4

I want to loop through rows and create another column which will tell me rank of each cell. So, for example "2" is the lowest so it gets 1. "9" is the highest to it gets 5 (highest rank, because there are 5 rows), "7" is second highest, so it gets 4, etc. So, I want to achieve this:

1
3
5
4
2

So, in other words. For every cell in new column, I need to put value how high relevant number in other column is. Can anyone help me with this? I'm using Google spreadsheet.

Answer 1

Google Sheets and Microsoft Excel have a RANK function that has the syntax...

RANK(value, data, [is_ascending])

Suppose your data is in cell range A1:A5 and you want the results in cell range B1:B5. Then you could place the formula...

=RANK($A1,$A$1:$A$5,1)

in cell B1 and fill down to cell B5.

Answer 2

having the numbers in A1 to A5 put in B1:

=ArrayFormula(MATCH(A1,SMALL(A$1:A$5,(ROW($1:$5))),0))

and auto-fill it down