O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

5.5. Ranking and Percentiles

Problem

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.

Solution

Use the built-in functions PERCENTILE, RANK, and PERCENTRANK. Or you can use the Rank and Percentile tool available in the Analysis ToolPak.

Discussion

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 RANK.

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required