How to Use the PERCENTILE Function in Google Sheets
=PERCENTILE(data, percentile). A percentile of 0.5 gives the same result as MEDIAN.
What PERCENTILE does
A percentile tells you where a value sits in a data set. The 90th percentile is the value that 90% of the data falls at or below. Teachers use it for test scores, and analysts use it for response times, where an average hides the slow cases. For the middle value on its own, see MEDIAN.
PERCENTILE syntax
=PERCENTILE(data, percentile)
data: the array or range containing the data set.percentile: the percentile to calculate withindata, as a decimal. In Google's samples it is written as0.67, so 0.5 means the 50th percentile.
Google's help page makes two points. The result may not be a value that appears in data, because the function interpolates between values. And setting percentile to 0.5 gives the same result as MEDIAN on the same data. The page does not describe the exact interpolation method, so check unusual cases against your own data.
A step-by-step example
Here are five scores in cells A1:A6.
| A | |
|---|---|
| 1 | Score |
| 2 | 10 |
| 3 | 20 |
| 4 | 30 |
| 5 | 40 |
| 6 | 50 |
=PERCENTILE(A2:A6, 0.5)
This returns 30, the same as =MEDIAN(A2:A6), because the 50th percentile is the median.
=PERCENTILE(A2:A6, 0.9)
This returns a value near the top of the list. Because the function interpolates, the answer can fall between two scores.
Common mistakes
- Entering 90 instead of 0.9. The percentile is a decimal in the help page's samples.
- Swapping the arguments. Data comes first, percentile second.
- Expecting a value from your list. The result can be interpolated.
Try it yourself
Use the scores above (A2:A6). Type your formula into the box, starting with =, then press Check.
Exercise 1: The 50th percentile
Write a formula that returns the 50th percentile of A2:A6.
Exercise 2: The 90th percentile
Write a formula that returns the 90th percentile of A2:A6.
What to learn next
- STDEV: how spread out your numbers are.
- MEDIAN: find the middle value of a range.
- LARGE and SMALL: find the nth largest or smallest value.
Syntax and rules in this guide were checked against Google's PERCENTILE help page. See all guides.