CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulaspreadsheetgoogle-sheets-api
Ish Thomas
Ish Thomas

Reputation: 2440

Google-spreadsheet formula to get number ranks in column

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.

Upvotes: 3

Views: 7454

Answers (2)

Bobby Orndorff
Bobby Orndorff

Reputation: 3335

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.

Upvotes: 8

Dirk Reichel
Dirk Reichel

Reputation: 7979

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

Upvotes: 1

Related Questions