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

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.

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.

PRODUCT & FEATURES

CAPTCHA Protection

RESOURCES

© 2023 Zapof