PERCENTILE Function in Google Sheets: Complete Guide
Percentiles divide your data into 100 equal parts, telling you what value falls at a specific point in your distribution. When someone says 'you scored in the 90th percentile,' they mean you...
Key Insights
- PERCENTILE calculates the value below which a given percentage of data falls, making it essential for benchmarking, outlier detection, and performance analysis in business contexts.
- Google Sheets offers three variants—PERCENTILE, PERCENTILE.INC, and PERCENTILE.EXC—each using different interpolation methods that can produce meaningfully different results on small datasets.
- Combining PERCENTILE with FILTER and ARRAYFORMULA unlocks powerful conditional analysis, letting you calculate percentiles across segments without manual data separation.
Introduction to PERCENTILE
Percentiles divide your data into 100 equal parts, telling you what value falls at a specific point in your distribution. When someone says “you scored in the 90th percentile,” they mean you performed better than 90% of the population.
This matters for practical data analysis. Salary negotiations rely on percentile benchmarks. Performance reviews use percentile rankings. Quality control teams set thresholds based on percentile cutoffs. Understanding percentiles transforms raw numbers into actionable context.
Google Sheets provides the PERCENTILE function to calculate these values directly from your data. Whether you’re analyzing test scores, sales figures, or response times, PERCENTILE gives you the statistical foundation for meaningful comparisons.
PERCENTILE Function Syntax and Parameters
The PERCENTILE function takes two arguments:
=PERCENTILE(data, percentile)
data: The range or array of numeric values you want to analyze. This can be a cell range like A1:A100, a named range, or an array of numbers.
percentile: A decimal value between 0 and 1 representing the percentile you want. Use 0.5 for the 50th percentile (median), 0.9 for the 90th percentile, and so on.
Here’s a basic demonstration:
| A |
|------------|
| 15 |
| 22 |
| 31 |
| 45 |
| 52 |
| 68 |
| 73 |
| 89 |
| 94 |
| 100 |
=PERCENTILE(A1:A10, 0.5) // Returns 60 (median)
=PERCENTILE(A1:A10, 0.25) // Returns 34.5 (first quartile)
=PERCENTILE(A1:A10, 0.9) // Returns 95.5 (90th percentile)
The function uses linear interpolation when the percentile falls between two data points. This means results aren’t always values that exist in your original dataset—they’re calculated positions within your distribution.
Basic Usage Examples
Let’s work through common percentile calculations with concrete data. Consider a dataset of monthly sales figures:
| A (Sales $) |
|-------------|
| 12500 |
| 18200 |
| 22100 |
| 25400 |
| 28900 |
| 31200 |
| 35600 |
| 42100 |
| 48700 |
| 67500 |
Finding the Median (50th Percentile)
The median represents the middle value of your distribution:
=PERCENTILE(A1:A10, 0.5)
// Returns 30050
This tells you half your sales months fell below $30,050 and half exceeded it.
Calculating Quartiles
Quartiles divide data into four equal parts. They’re fundamental for box plots and distribution analysis:
=PERCENTILE(A1:A10, 0.25) // Q1: 23275 (first quartile)
=PERCENTILE(A1:A10, 0.5) // Q2: 30050 (median)
=PERCENTILE(A1:A10, 0.75) // Q3: 40475 (third quartile)
The interquartile range (Q3 - Q1) equals $17,200, representing the spread of your middle 50% of data.
90th Percentile Analysis
For identifying high performers or setting stretch goals:
=PERCENTILE(A1:A10, 0.9)
// Returns 53580
Only 10% of months exceeded $53,580 in sales. This becomes your benchmark for exceptional performance.
PERCENTILE vs. PERCENTILE.INC vs. PERCENTILE.EXC
Google Sheets offers three percentile functions that differ in how they handle boundary values:
PERCENTILE and PERCENTILE.INC are identical—they use inclusive interpolation where percentile values of 0 and 1 return the minimum and maximum values respectively.
PERCENTILE.EXC uses exclusive interpolation, excluding the endpoints. It requires percentile values strictly between 0 and 1 (exclusive), calculated as k/(n+1) where n is the data count.
Here’s the practical difference:
| A |
|-------|
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
=PERCENTILE.INC(A1:A5, 0) // Returns 10 (minimum)
=PERCENTILE.INC(A1:A5, 1) // Returns 50 (maximum)
=PERCENTILE.INC(A1:A5, 0.25) // Returns 20
=PERCENTILE.EXC(A1:A5, 0) // Returns #NUM! error
=PERCENTILE.EXC(A1:A5, 1) // Returns #NUM! error
=PERCENTILE.EXC(A1:A5, 0.25) // Returns 17.5
When to use each:
Use PERCENTILE.INC (or PERCENTILE) for most business applications. It’s intuitive and handles edge cases gracefully.
Use PERCENTILE.EXC when your statistical methodology specifically requires exclusive percentiles, common in certain academic or scientific contexts. It’s also preferred when you need consistency with Excel’s PERCENTILE.EXC function for cross-platform work.
For datasets larger than 30 observations, the differences become negligible. The choice matters most with small samples.
Practical Applications
Identifying Top 10% Performers
You have employee performance scores and need to flag top performers for bonuses:
| A (Employee) | B (Score) | C (Top 10%?) |
|--------------|-----------|------------------------------------------------|
| Alice | 78 | =IF(B2>=PERCENTILE($B$2:$B$11, 0.9), "Yes", "No") |
| Bob | 92 | =IF(B3>=PERCENTILE($B$2:$B$11, 0.9), "Yes", "No") |
| Carol | 85 | ... |
| David | 67 | ... |
This formula compares each score against the 90th percentile threshold, automatically identifying your top performers regardless of how scores change.
Salary Benchmarking Analysis
Compare individual salaries against market percentiles:
| A (Role) | B (Salary) | C (Market Data) | D (Percentile Rank) |
|----------------|------------|------------------|------------------------------------------|
| Engineer | 95000 | 75000 | =PERCENTRANK($C$2:$C$20, B2) |
| | | 82000 | |
| | | 88000 | |
| | | 95000 | |
| | | 105000 | |
Combine this with percentile thresholds for compensation bands:
=IF(B2 < PERCENTILE($C$2:$C$20, 0.25), "Below Market",
IF(B2 < PERCENTILE($C$2:$C$20, 0.75), "At Market", "Above Market"))
Outlier Detection Using Percentile Thresholds
Flag data points outside the typical range using the 5th and 95th percentiles:
| A (Response Time ms) | B (Outlier?) |
|----------------------|-----------------------------------------------------------------|
| 120 | =IF(OR(A2<PERCENTILE($A$2:$A$100,0.05), A2>PERCENTILE($A$2:$A$100,0.95)), "Outlier", "Normal") |
| 145 | ... |
| 890 | ... |
This approach is more robust than using standard deviations for skewed distributions.
Combining PERCENTILE with Other Functions
PERCENTILE with FILTER for Conditional Percentiles
Calculate percentiles for specific segments without separating data:
| A (Region) | B (Sales) |
|------------|-----------|
| North | 45000 |
| South | 32000 |
| North | 52000 |
| South | 28000 |
// 75th percentile for North region only
=PERCENTILE(FILTER(B:B, A:A="North"), 0.75)
// Median sales for South region
=PERCENTILE(FILTER(B:B, A:A="South"), 0.5)
This eliminates manual filtering and keeps your analysis dynamic.
Using ARRAYFORMULA for Multiple Percentiles
Calculate several percentiles at once:
// In cells D1:D4, calculate quartiles
=ARRAYFORMULA(PERCENTILE(A1:A100, {0, 0.25, 0.5, 0.75, 1}))
This returns an array of five values: minimum, Q1, median, Q3, and maximum—everything needed for a box plot summary.
Combining with COUNTIF for Distribution Analysis
Determine what percentage of values fall below a threshold:
// What percentage of sales are below the 75th percentile threshold?
=COUNTIF(B2:B100, "<"&PERCENTILE(B2:B100, 0.75)) / COUNT(B2:B100)
This validates your percentile calculations and helps communicate results to stakeholders who think in terms of counts rather than statistical measures.
Common Errors and Troubleshooting
Invalid Percentile Values
The percentile parameter must be between 0 and 1. Using 90 instead of 0.9 returns a #NUM! error:
=PERCENTILE(A1:A10, 90) // #NUM! error
=PERCENTILE(A1:A10, 0.90) // Correct
Empty Cells and Non-Numeric Data
PERCENTILE ignores empty cells and text values, which is usually helpful but can cause unexpected results:
| A |
|-------|
| 10 |
| (empty) |
| 30 |
| "N/A" |
| 50 |
=PERCENTILE(A1:A5, 0.5) // Returns 30, based on only 3 values
If you need to treat blanks as zeros, use:
=PERCENTILE(IF(A1:A10="", 0, A1:A10), 0.5)
Error Handling with IFERROR
Wrap percentile calculations to handle edge cases gracefully:
=IFERROR(PERCENTILE(A1:A10, 0.9), "Insufficient data")
This prevents formula errors from cascading through dependent calculations and provides meaningful feedback when data is missing.
Single Value Datasets
PERCENTILE returns the single value for any percentile when your dataset contains only one number. This is technically correct but often indicates a data problem:
=IF(COUNT(A1:A10) < 5,
"Need more data",
PERCENTILE(A1:A10, 0.9))
Set minimum thresholds appropriate for your analysis context.