You need to compute certain percentiles of a set of data and you'd also like to compute the rank of certain values in the dataset.
Use the built-in functions
PERCENTRANK. Or you can use the Rank and Percentile tool
available in the Analysis ToolPak.
By way of example, let's assume your dataset consists of the values 1, 3, 5, 7, 9, 2, 4, 6, 8, 10, and 0. Let's also assume this dataset resides on a spreadsheet in the cell range C18:C28. (This is a rather simple dataset for example purposes; in practice your dataset can be anything and it need not be sorted.)
To compute the 25% percentile, use the formula
=PERCENTILE(C18:C28,0.25), which returns a value of 2.5. To compute the 95% percentile, use
=PERCENTILE(C18:C28,0.95), which returns a value of 9.5 as expected.
To compute the rank of a given value, use the formula
=RANK(2,C18:C28,1). The rank of the value 2 in this dataset is 3, assuming the dataset is sorted in ascending order. If you want to compute the rank of a value as though the dataset were in descending order, then use a value of 0 for the third argument in the call to
If you'd like to compute the rank of a value in percentage terms, then use the
PERCENTRANK function. For example,
=PERCENTRANK(C18:C28,2,2) returns the rank of the value 2 as 20%. The second argument in this function call represents the value whose rank is sought. The third argument is the number of significant digits for the result ...