PERCENTRANK

Returns the percentage rank of a number in a list of numbers.

Syntax:

PERCENTRANK(numberlist, number)

number is the number whose percentage rank is to be found within the unordered range or array of numbers numberlist.

PERCENTRANK internally assigns a rank r to number, where 0 is the rank of the lowest number, 1 of the next lowest and so on. If number is not in numberlist, it is assigned a fractional rank proportionately between the rank of the numbers on either side (see the examples). Where N is the count of numbers in numberlist, PERCENTRANK returns r / (N-1), which is a fraction between 0 and 1 inclusive.

This function is of limited use with a small list of numbers. It may be useful for example when determining where a score lies within all scores for a test, in which case the formula returned can be stated as L / (N-1), where L is the count of scores which are lower than number.

Example:

PERCENTRANK(A2:A99, A7)

where cells A2:A99 contain scores from a test, returns the percent rank (as a fraction) for the student whose test score is in cell A7.

The next two examples illustrate the calculation, but have little practical application:

PERCENTRANK({3, 4, 9, 11, 12}, 4)

returns 0.25.

PERCENTRANK({9, 3, 7, 8}, 6)

returns 0.25. The lowest number 3 is assigned rank '0'; the next highest number 7 is assigned rank '1'. 6 is assigned a rank proportionately between these, rank '0.75'. There are 4 numbers in the list, so the result is calculated as 0.75/(4-1) = 0.75/3 = 0.25.

Application:

Student Performance in a Class

The PERCENTRANK function is an excellent way to understand a data point's relative position within a dataset. A common application is evaluating a student's performance relative to their classmates.

Scenario: A math teacher wants to assess how well a student, let's say "Emily," performed on a recent exam compared to the rest of the class. The teacher has the scores of all 15 students in the class.

The PERCENTRANK function will tell us the percentage of scores that are less than or equal to a specific score.

Student Exam Scores Table

	Student	Score
	A	B
1	Sarah	85
2	David	78
3	Mark	92
4	Emily	88
5	Chris	65
6	Anna	95
7	John	88
8	Olivia	72
9	Ben	80
10	Grace	90
11	Liam	75
12	Mia	88
13	Jacob	82
14	Chloe	68
15	Noah	98

Let's use PERCENTRANK to evaluate Emily's score of 88.

The function would be written as: PERCENTRANK(array, x) where:

array is the range of scores (B1:B15 in a spreadsheet).
x is Emily's score (88).

Interpretation:

The PERCENTRANK for Emily's score of 88 is 0.571 or 57.1%.

This means that 57.1% of the students in the class scored a grade equal to or lower than Emily's score of 88. In other words, Emily performed better than approximately 57% of her classmates on this exam. This provides a much more meaningful context than just seeing her raw score alone. It tells the teacher and Emily exactly where she stands relative to her peers.

Result for PERCENTRANK(array, x):

0.571