Dear Analyst Episode 24: Finding and ranking percentiles
Podcast: Play in new window | Download
Subscribe: Google Podcasts | Spotify | Stitcher | TuneIn | RSS
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:
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
=RANK.EQ(value, data, 1)/COUNT(data)
In the Google Sheet, the full formula for cell D15 is:
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.
Other Podcasts & Blog Posts
In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:
No comments yet.