If you want to know the value below which a given percentage of your data falls, the PERCENTILE function in Google Sheets gives you that number in one shot. Hand it a range and a fraction between 0 and 1, and it returns the cut-off point.
In this article, I’ll walk through five practical PERCENTILE setups. You’ll see the 50th (median), the 90th, the 25th, a multi-column range, and a spread measure using two percentiles together.
PERCENTILE Function Syntax in Google Sheets
Here is how you write the PERCENTILE function.
=PERCENTILE(data, percentile)
- data is the range or array of numeric values.
- percentile is a decimal between 0 and 1. For example, 0.5 is the 50th percentile, 0.9 is the 90th.
PERCENTILE uses linear interpolation between the two nearest values when the requested percentile doesn’t land exactly on a data point. You’ll see this in the examples below where the result is a decimal even though every input is a whole number.
When to Use PERCENTILE Function
- Find the median of a list (50th percentile) without sorting it first.
- Set a service-level threshold like P90 or P95 for response times.
- Spot the bottom or top quartile of sales, scores, or revenue.
- Measure the spread of a dataset using P75 minus P25 (the interquartile range).
- Compare distributions side by side across different products, teams, or time periods.
Example 1: Median Score With the 50th Percentile
Let’s start with the most common percentile, the 50th. It’s the same as the median.
Below is the dataset. Column A has the student name and column B has the score, across ten rows.
I want the median score from the list.
Here is the formula:
=PERCENTILE(B2:B11, 0.5)
PERCENTILE sorts the ten scores internally, then finds the value halfway through that sorted list. With an even count, the median sits between the two middle values (82 and 85), and PERCENTILE interpolates to 83.5.
You’d get the same result from AVERAGE of those two middle values, but PERCENTILE handles the sorting and the lookup in one step.
Pro Tip: If you specifically want a quartile (the 25th, 50th, or 75th percentile), the QUARTILE function takes a quartile number (1, 2, or 3) instead of a decimal. Same math, slightly cleaner syntax for those three points.
Example 2: 90th Percentile of Response Times
Engineering teams often track P90 or P95 to spot slow requests without letting outliers dominate.
Below is the dataset. Column A has the request label and column B has the response time in milliseconds, across ten rows.
I want the 90th percentile of the response times.
Here is the formula:
=PERCENTILE(B2:B11, 0.9)
The result is 419. Ninety percent of the response times fall at or below 419 ms, and only the slowest ten percent are above that. The exact value comes from linear interpolation between 410 (R6) and 500 (R10), the two values closest to the 90th-percentile position.
P90 is more robust than the maximum because one outlier can’t drag it. The 500 ms request is still in the dataset, but it doesn’t define the threshold.
Example 3: First Quartile (Q1) of Sales
The 25th percentile (also called Q1) shows where the bottom quarter of values sits.
Below is the dataset. Column A has the sales rep and column B has the sales figure, across eight rows.
I want the 25th percentile of the sales figures.
Here is the formula:
=PERCENTILE(B2:B9, 0.25)
PERCENTILE returns 902.5. A quarter of the reps sit at or below 902.5 in sales, three-quarters sit above it. Anyone below the Q1 line is in the bottom quartile and might warrant a coaching look.
The decimal is again interpolation kicking in. The values 850 and 920 straddle the Q1 position, and PERCENTILE blends them to 902.5.
Example 4: Percentile Across a Multi-Column Range
PERCENTILE accepts a 2D range, so you can pass a whole block of numbers at once.
Below is the dataset. Column A has the employee name and columns B through D have January, February, and March scores, across five rows.
I want the median across all fifteen monthly scores (not per employee, but pooled together).
Here is the formula:
=PERCENTILE(B2:D6, 0.5)
PERCENTILE flattens B2:D6 into a single pool of fifteen numbers and finds the 50th percentile of that pool. The result is 85.
This is the easy way to get a department-wide median without first reshaping the data into one column.
Example 5: Interquartile Range (IQR) From Two Percentiles
Subtracting the 25th percentile from the 75th gives you the interquartile range, a popular spread measure.
Below is the dataset. Column A has the request label and column B has the latency in milliseconds, across twelve rows.
I want the IQR (P75 minus P25) of the latency values.
Here is the formula:
=PERCENTILE(B2:B13, 0.75)-PERCENTILE(B2:B13, 0.25)
How this formula works:
- The first PERCENTILE call returns the 75th-percentile latency.
- The second PERCENTILE call returns the 25th-percentile latency.
- Subtracting them gives the IQR, which is the spread of the middle half of the data.
For this dataset the IQR comes out to 7 milliseconds. That’s a tight middle 50%, even though individual values range from 38 to 60.
Tips & Common Mistakes
- The second argument must be between 0 and 1. Passing 90 instead of 0.9 returns
#NUM!. Percent points like 90 go in as 0.9, not 90. - PERCENTILE interpolates between values. A result of 83.5 may not actually appear in your dataset. If you need the nearest real data point, use PERCENTILE.EXC or LARGE/SMALL with manual ranking.
- Blanks and text are ignored. Empty cells inside the range don’t count toward the percentile calculation. Text values are skipped too. If all the cells are non-numeric, PERCENTILE returns
#NUM!.
PERCENTILE is the cleanest way to pull a cut-off value out of a Google Sheets range. You’ve now seen it for the median, P90, Q1, a 2D range, and an IQR setup that uses two percentiles together.
List of All Google Sheets Functions
Related Google Sheets Functions / Articles: