Rank and percentile in Google Sheets

The Rank and Percentile tool sorts a list from highest to lowest and gives each value its rank and percentile. Example: third-quarter sales for 12 sales reps, listed in employee order.

	A
1	Q3 sales
2	48200
3	51750
4	39900
5	62300
6	51750
7	44100
8	57800
9	36500
10	49950
11	68400
12	42700
13	55300

Run the tool

Open Extensions, StatPak, Data Analysis and choose Rank and Percentile.
Input range: A1:A13, Grouped by: Columns.
Tick Labels in first row or column and click Run.

Point	Q3 sales	Rank	Percent
10	68400	1	100.00%
4	62300	2	90.90%
7	57800	3	81.80%
12	55300	4	72.70%
2	51750	5	54.50%
5	51750	5	54.50%
9	49950	7	45.40%
1	48200	8	36.30%
6	44100	9	27.20%
11	42700	10	18.10%
3	39900	11	9.00%
8	36500	12	0.00%

How to read the output

Point is the value's position in the original list, so you can trace it back. Point 10 (row 11 of the sheet) is the top rep with 68400.
Rank counts from 1 for the largest value. Tied values share a rank and the next rank is skipped: points 2 and 5 both have rank 5, and the next value gets rank 7.
Percent is the share of the other values that are below this one. The top value is 100% and the bottom value is 0%. Percentiles are truncated to three decimal places, as in Excel: point 9 has 5 of the other 11 values below it, which is 0.4545..., and shows as 45.40%.

Select several columns to rank each one separately; the tool places one Point, value, Rank and Percent block per column.

With functions

The tool uses the same definitions as these functions, which keep the original row order:

Rank: =RANK.EQ(A2, $A$2:$A$13). Add a third argument of 1 to rank from smallest to largest.
Percentile: =PERCENTRANK.INC($A$2:$A$13, A2), formatted as a percentage.
Sorted list: =SORT(A2:A13, 1, FALSE).

To find the value at a given percentile, the reverse question, use =PERCENTILE.INC(A2:A13, 0.9).

Rank and percentile in Google Sheets

Run the tool

How to read the output

With functions

Related guides