Dear Analyst Episode 24: Finding and ranking percentiles

I discuss how to calculate percentiles in Excel or Google Sheets using the PERCENTILE function. With the PERCENTILE function, you can calculate the value that would represent nth percentile in your list of values. This is not exactly the calculation I was looking for. Instead, what if you wanted to know what the rank percentile is (as a percentage) for a given value in your data set? See the image below from this Google Sheet to see the difference between these two scenarios:

Percentile as a value vs. percentile as a %

It’s a really small difference, but I simply wanted to calculate which percentile a given value fell in given my data set. In the Google Sheet, you’ll see a list of test scores. Is Dan’s test score in the 30th percentile? 50th? There’s no built in function to find this.

If you know the percentile, it’s easy to calculate the value that the percentile represents with this formula:

=PERCENTILE(data, percentile)

data is simply your list of values and percentile is anything between 0 and 1. To answer my original question, you need to use a combination of RANK() and the COUNT() functions:

=RANK.EQ(value, data, 1)/COUNT(data)

In the Google Sheet, the full formula for cell D15 is:

=rank.eq(C15,$C$15:$C$20,1)/count($C$15:$C$180)

The reason you need the 3rd argument set at 1 for the RANK.EQ function is so that you reverse the list of values that you are ranking (the greatest value in your list ends up being ranked last). This way, when you divide the result of the RANK.EQ function by the result of the COUNT function, you’ll get the actual percentage of the percentile.