Purpose
Return value
Syntax
=PERCENTRANK(array,x,[significance])- array - Array of data values.
- x - Value to rank.
- significance - [optional] Number of significant digits in result. Defaults to 3.
How to use
The PERCENTRANK function returns the relative standing of a value within a data set as a percentage. For example, a test score greater than 80% of all test scores is said to be at the 80th percentile. In this case, PERCENTRANK will assign a rank of .80 to the score. To use PERCENTRANK, provide an array of values (typically a range) and the value to rank, x. In the example shown, the formula in C5 is:
=PERCENTRANK(data,B5)
where data is the named range B5:B12. As the formula is copied down, it returns the rank of each value in column B as a decimal value. To display the results in column C as a percentage, apply the percentage number format. The table in the range F4:G15 is for reference only. It uses the PERCENTILE function in column G to calculate a percentile for each value in column F. A percentile is the value below which a given percentage of observations in a group fall.
Note: Microsoft classifies PERCENTRANK as a "compatibility function", now replaced by the PERCENTRANK.INC function.
Inclusive vs. Exclusive
Starting with Excel 2010, the PERCENTRANK function has been replaced by two functions: PERCENTRANK.INC and PERCENTRANK.EXC. The INC version represents "inclusive" behavior, and the EXC version represents "exclusive" behavior. Both formulas use the same arguments.
- Use the PERCENTRANK.EXC function to determine the percentage rank exclusive of the first and last values in the array.
- Use the PERCENTRANK.INC or PERCENTRANK to find the percentage rank inclusive of the first and last values in the array.
Notes
- If x does not exist in the array, PERCENTRANK interpolates to find the percentage rank.
- When significance is omitted PERCENTRANK returns three significant digits (0.xxx)
Related functions
SMALL Function
The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.
LARGE Function
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.
RANK Function
The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest (i.e. top sales) as well as smallest to largest (i.e. fastest time).
PERCENTILE Function
The Excel PERCENTILE function calculates the "kth percentile" for a set of data. A percentile is a value below which a given percentage of values in a data set fall. You can use PERCENTILE to determine the 90th percentile, the 80th percentile, etc.
QUARTILE Function
The Excel QUARTILE function returns the quartile (each of four equal groups) for a given set of data. QUARTILE can return minimum value, first quartile, second quartile, third quartile, and max value.
