PERCENTILE
Statistical
Returns the k-th percentile of values in a range.
Translations
EnglishPERCENTILE
FrenchCENTILE
SpanishPERCENTIL
GermanQUANTIL
ItalianPERCENTILE
PortuguesePERCENTIL
DutchPERCENTIEL
PolishPERCENTYL
RussianПРОЦЕНТИЛЬ
TurkishYÜZDEBİRLİK
CzechPERCENTIL
HungarianPERCENTILIS
SwedishPERCENTIL
DanishFRAKTIL
FinnishPROSENTTIPISTE
Syntax
PERCENTILE(array, k)Arguments
arrayRange of datakPercentile value (0 to 1)
Examples
=PERCENTILE(A1:A100,0.5)50th percentile
Same as median
=PERCENTILE(Scores,0.9)90th percentile
Top 10% threshold
=PERCENTILE(A:A,0.25)25th percentile
First quartile
Tips & Best Practices
- •k=0.5 equals MEDIAN
- •k must be between 0 and 1
- •Use PERCENTILE.INC or PERCENTILE.EXC for variants
Common Mistakes
- •Confusing the k argument's format - it must be a decimal between 0 and 1 (like 0.9 for the 90th percentile), and entering 90 instead of 0.9 raises a #NUM! error
- •Using the legacy PERCENTILE function without realizing PERCENTILE.INC and PERCENTILE.EXC now offer more explicit control over whether the endpoints of the range are included in the calculation
- •Applying PERCENTILE to a very small dataset and expecting a value that actually exists in the data - percentile calculations often interpolate between two existing values, producing a result that isn't literally present in the range
Related Functions
PERCENTILE.INCThe modern replacement for PERCENTILE, functionally identical and inclusive of the range's endpoints.PERCENTILE.EXCCalculates a percentile excluding the range's endpoints, a stricter alternative to PERCENTILE.INC and legacy PERCENTILE.QUARTILECalculates specific percentiles (0th, 25th, 50th, 75th, 100th) using simpler, preset arguments instead of PERCENTILE's fully flexible k value.Frequently Asked Questions
Why does PERCENTILE return a #NUM! error?
The k argument is likely outside the valid 0 to 1 range - use 0.9, not 90, for the 90th percentile.
Should I use PERCENTILE or PERCENTILE.INC?
PERCENTILE.INC is the modern equivalent with identical behavior - PERCENTILE is kept mainly for compatibility with older files.
Why doesn't PERCENTILE return a value that's actually in my data?
Percentile calculations often interpolate between the two nearest values in the sorted dataset, so the result can fall between existing data points rather than matching one exactly.
Need to translate a formula using PERCENTILE?
Use our translator to convert your complete formula