Excel includes six functions used for finding rank and percentile on values in a data set.
LARGE
Use the LARGE function to find the kth largest value in a data set. While you can use the maximum function to find the largest value in a data set, you can use the LARGE function to find the runner up or third-place value. The LARGE function uses the following syntax:
=LARGE (data set range, k)
SMALL
Use the SMALL function to find the kth smallest value in a data set. Although you can use the MIN function to find the smallest value in a data set, you can use the SMALL function to find multiple values at the bottom. The SMALL function uses the following syntax:
=SMALL (data set range, k)
RANK
To find the rank of a value in a data set relative to other values in the data set, you can use the RANK function. The RANK function uses the following syntax:
=RANK (number, data set, order)
where number is the number whose rank you want to find, data set is the list of values against which you want to rank it, and order tells Excel to rank in ascending or descending order. Enter a nonzero value to rank the numbers in ascending order. Enter zero or leave the order parameter blank to rank in descending order.
NOTE: If the data set includes duplicate values, Excel gives these values the same rank. This effects the ranks of subsequent numbers. For example, if the value 3 appears twice and has a rank of 6, then 4 would have a rank of 8, and no value would have a rank of 7.
PERCENTRANK
To find the rank of a value in a data set as a percentage, you can use the PERCENTRANK function. The PERCENTRANK function uses the following syntax:
=PERCENTRANK (data set, x, significance)
where x is the value whose rank you want to find, data set is the list of values against which you want to rank it, and significance tells Excel the number of significant digits it should use for the value. The value you get is between 0 and 1; you need to multiply by 100 to get the actual percent ranking.
PERCENTILE
Percentile is a measure that locates where a value stands in a data set. The kth percentile divides the data so that at least p percent are of this value or less and (100-p) percent are this value or more. If you have a set of data and need to find the value at a certain percentile, you use the PERCENTILE function in Excel. The PERCENTILE function uses the following syntax:
=PERCENTILE (data set range, k)
For example, if a score needs to be above the 80th percentile for admission, you can find which value defines that percentile by entering 0.8 for k.
QUARTILE
The QUARTILE function in Excel is closely related to the PERCENTILE function. People often use quartiles, which order the values in a data set into quarters, when dividing populations into groups based on sales and survey data. The first quartile is the 25th percentile. The second quartile is the median, or 50th percentile. The third quartile is the 75th percentile. The fourth quarter is the maximum value. The QUARTILE function uses the following syntax:
=QUARTILE (data set range, quartile)
For the quartile parameter, enter 0 for the minimum value, 1 for the first quartile, 2 for the second quartile, 3 for the third quartile, or 4 for the maximum value.