Excel PERCENTILE and QUARTILE Functions
last modified October 5, 2026
The PERCENTILE and QUARTILE functions are statistical
tools in Excel that describe the shape of a dataset. PERCENTILE returns the
value below which a chosen percentage of the data falls, while QUARTILE returns
values at the four fixed points that split the data into quarters. This
tutorial provides a comprehensive guide to both functions. You'll learn their
syntax and arguments, the rule Excel uses to interpolate between values, the
inclusive and exclusive variants, and the errors they can raise.
PERCENTILE/QUARTILE Basics
PERCENTILE returns the value at a specific percentile in a dataset.
QUARTILE is a fixed-point special case that returns values at the
0%, 25%, 50%, 75%, and 100% points. Both answer the same question: where does a
value stand relative to the rest of the data?
| Function | Description |
|---|---|
| PERCENTILE | Returns value at a given percentile in the range 0 to 1 |
| PERCENTILE.INC | Inclusive version, same result as PERCENTILE (Excel 2010+) |
| PERCENTILE.EXC | Exclusive version, excludes the 0% and 100% endpoints (Excel 2010+) |
| QUARTILE | Returns value at a quartile in the range 0 to 4 |
| QUARTILE.INC | Inclusive version, same result as QUARTILE (Excel 2010+) |
| QUARTILE.EXC | Exclusive version, accepts quart values 1, 2, and 3 only (Excel 2010+) |
This table lists all six variants. Microsoft recommends the .INC and .EXC names
in new work, but the original names remain available for backward compatibility
and always behave like the inclusive versions: PERCENTILE is
identical to PERCENTILE.INC, and QUARTILE is
identical to QUARTILE.INC. The inclusive versions include the 0th
and 100th percentiles, while the exclusive versions exclude them, and QUARTILE
is simply PERCENTILE asked at five specific points.
Syntax and Arguments
| Component | Description |
|---|---|
| Function Name | PERCENTILE, QUARTILE |
| PERCENTILE Syntax | =PERCENTILE(array, k) |
| QUARTILE Syntax | =QUARTILE(array, quart) |
| Arguments | array - the numbers to analyze; k - percentile from 0 to 1; quart - quartile from 0 to 4 |
| Return Value | The value standing at the requested percentile or quartile |
This table breaks down the essential components of both functions. They share the same first argument and differ only in the second: PERCENTILE takes a fractional percentile, and QUARTILE takes a whole-number quartile selector.
| Argument | Meaning |
|---|---|
| array | Required. The range or array of data that defines relative standing. Text, logical values, and empty cells inside it are ignored, while numbers such as zero are included. At least one number is required. |
| k | Required for PERCENTILE. The percentile to return, written as a decimal in the range 0 to 1, inclusive. Use 0.9 for the 90th percentile. |
| quart | Required for QUARTILE. The quartile to return, an integer from 0 to 4. A non-integer value such as 2.9 is truncated to 2. |
The k argument is a fraction, not a percentage: 0.5 is the median and 0.9 is
the 90th percentile. Passing 90 instead of 0.9 raises #NUM!, and k
outside the range 0 to 1 also raises #NUM!. k that cannot be read
as a number, such as the text "ninety", raises #VALUE!. The quart
argument accepts only 0, 1, 2, 3, and 4; any other value raises
#NUM!. Inside a range both functions skip text, logical values,
and empty cells, count cells holding zero, and pass any error value in the
range straight through to the result.
The Interpolation Rule
Neither function is limited to numbers that already exist in the data. Both
sort the values and place the requested percentile at position
1 + (n - 1) * k, where n is the count of numbers. When that
position falls between two values, Excel interpolates linearly between them.
The Microsoft documentation states it directly: if k is not a multiple of
1/(n - 1), PERCENTILE interpolates to determine the value at the k-th
percentile.
=PERCENTILE.INC({78, 85, 88, 92, 95}, 0.9) ' 93.8
With five sorted values the position is 1 + 4 * 0.9 = 4.6, so the result lies
between the fourth value (92) and the fifth (95): 92 + 0.6 * (95 - 92) = 93.8.
When the position lands on a whole number, the exact data value is returned,
which is why the median of an odd-sized set is always one of the data points.
The exclusive variants interpolate at position k * (n + 1) instead,
and that is why they cannot reach the endpoints of the data.
QUARTILE Quartile Mapping
The quart argument of QUARTILE is a selector for five fixed points
of the distribution.
| quart | Returns |
|---|---|
| 0 | Minimum value (0th percentile) |
| 1 | First quartile (25th percentile) |
| 2 | Median (50th percentile) |
| 3 | Third quartile (75th percentile) |
| 4 | Maximum value (100th percentile) |
The quartile 0, 2, and 4 results are identical to MIN, MEDIAN, and MAX of the
same range. QUARTILE.EXC follows a different mapping because it
excludes the extremes: it accepts only quart 1, 2, and 3, so quart 0 and
quart 4 return #NUM!.
Basic PERCENTILE Example
This example demonstrates finding the 90th percentile in a dataset of test scores. The 90th percentile is the value below which 90% of the data falls, and because Excel interpolates, the result does not have to be one of the scores.
| A | B |
|---|---|
| 78 | |
| 85 | |
| 92 | |
| 88 | |
| 95 | |
| =PERCENTILE.INC(A1:A5, 0.9) |
=PERCENTILE.INC(A1:A5, 0.9)
This formula calculates the 90th percentile of the test scores in A1:A5. The result is 93.8, an interpolated value between 92 and 95, because the position 1 + 4 * 0.9 = 4.6 falls between the fourth and fifth scores. Writing k as 0.9 rather than 90 is essential, and the .INC version accepts k of 0 and 1 as well, returning the minimum and maximum of the range.
QUARTILE Example with Sales Data
This example uses QUARTILE to analyze quarterly sales data distribution. It shows how to find all five quartile points (minimum, Q1, median, Q3, maximum).
| A | B |
|---|---|
| 12500 | |
| 18700 | |
| 14300 | |
| 21000 | |
| 16500 | |
| =QUARTILE.INC(A1:A5, 0) | |
| =QUARTILE.INC(A1:A5, 1) | |
| =QUARTILE.INC(A1:A5, 2) | |
| =QUARTILE.INC(A1:A5, 3) | |
| =QUARTILE.INC(A1:A5, 4) |
=QUARTILE.INC(A1:A5, 0) ' Minimum =QUARTILE.INC(A1:A5, 1) ' First quartile (25%) =QUARTILE.INC(A1:A5, 2) ' Median (50%) =QUARTILE.INC(A1:A5, 3) ' Third quartile (75%) =QUARTILE.INC(A1:A5, 4) ' Maximum
These formulas calculate all five quartile points for the sales data. Quartile 0 is the minimum (12500), Q1 is 14300, the median is 16500, Q3 is 18700, and quartile 4 is the maximum (21000). The five sorted values are 12500, 14300, 16500, 18700, and 21000, so Q1 and Q3 land exactly on data values. This gives a complete picture of the data distribution.
PERCENTILE.EXC vs PERCENTILE.INC
This example demonstrates the difference between the inclusive and exclusive versions of PERCENTILE. The .EXC version excludes the 0% and 100% percentiles, so it never returns the raw minimum or maximum of the range.
| A | B | C |
|---|---|---|
| 15 | =PERCENTILE.INC(A1:A5, 0) | =PERCENTILE.EXC(A1:A5, 0) |
| 22 | =PERCENTILE.INC(A1:A5, 0.5) | =PERCENTILE.EXC(A1:A5, 0.5) |
| 30 | =PERCENTILE.INC(A1:A5, 0.9) | =PERCENTILE.EXC(A1:A5, 0.9) |
| 18 | =PERCENTILE.INC(A1:A5, 1) | =PERCENTILE.EXC(A1:A5, 1) |
| 25 |
=PERCENTILE.INC(A1:A5, 0) ' 15, the minimum =PERCENTILE.EXC(A1:A5, 0) ' #NUM! =PERCENTILE.INC(A1:A5, 0.5) ' 22 =PERCENTILE.EXC(A1:A5, 0.5) ' 22 =PERCENTILE.INC(A1:A5, 0.9) ' 28 =PERCENTILE.EXC(A1:A5, 0.9) ' #NUM! =PERCENTILE.INC(A1:A5, 1) ' 30, the maximum =PERCENTILE.EXC(A1:A5, 1) ' #NUM!
The .INC version works with k of 0 and 1, returning the minimum and maximum.
The .EXC version returns #NUM! for both, and also for 0.9 in this
example. For n numbers, PERCENTILE.EXC can only evaluate a k between 1/(n + 1)
and n/(n + 1); with five values that range is about 0.167 to 0.833, so 0.9 is
too close to 100% to interpolate. The median at 0.5 is the one point where both
versions agree here, because the median of an odd count is a real data value.
Using QUARTILE for Outlier Detection
This example shows how to use QUARTILE to identify potential outliers using the interquartile range (IQR) method. IQR is Q3-Q1, and outliers are typically values below Q1-1.5*IQR or above Q3+1.5*IQR.
| A | B |
|---|---|
| 12 | =QUARTILE.INC(A1:A10,1) |
| 15 | =QUARTILE.INC(A1:A10,3) |
| 18 | =B1-1.5*(B2-B1) |
| 20 | =B2+1.5*(B2-B1) |
| 22 | |
| 25 | |
| 28 | |
| 32 | |
| 35 | |
| 120 |
=QUARTILE.INC(A1:A10,1) ' Q1 (18.5) =QUARTILE.INC(A1:A10,3) ' Q3 (31) =B1-1.5*(B2-B1) ' Lower bound (-0.25) =B2+1.5*(B2-B1) ' Upper bound (49.75)
This calculates Q1 (18.5) and Q3 (31), so the interquartile range is 31 - 18.5 = 12.5. The lower bound is 18.5 - 1.5 * 12.5 = -0.25 and the upper bound is 31 + 1.5 * 12.5 = 49.75. The value 120 exceeds the upper bound, identifying it as a potential outlier, while every other value stays inside the bounds. This method is commonly used in statistical analysis.
PERCENTILE with Conditional Data
This advanced example combines PERCENTILE with FILTER to calculate percentiles for specific subsets of data. Here we find the 75th percentile for sales in the East region only.
| A (Region) | B (Sales) | C |
|---|---|---|
| East | 12000 | |
| West | 15000 | |
| East | 18000 | |
| North | 9000 | |
| East | 21000 | |
| =PERCENTILE.INC(FILTER(B1:B5,A1:A5="East"),0.75) |
=PERCENTILE.INC(FILTER(B1:B5,A1:A5="East"),0.75)
This formula first filters the sales down to the East region (12000, 18000,
21000) and then takes the 75th percentile of those three numbers. The position
is 1 + 2 * 0.75 = 2.5, so the result lies halfway between 18000 and 21000,
which is 19500. The FILTER function is available in Microsoft 365
and Excel 2021; in earlier versions, build the subset with an array formula
entered with Ctrl+Shift+Enter:
=PERCENTILE(IF(A1:A5="East", B1:B5), 0.75). This technique is
powerful for analyzing specific segments within larger datasets.
Ignoring Text and Blanks
Ranges passed to these functions are cleaned before the calculation: text, logical values, and empty cells are skipped, while cells that hold the number zero are kept. This example mixes all of those cases in one range.
| A | B |
|---|---|
| 10 | |
| East | |
| 0 | |
| 20 | |
| =PERCENTILE(A1:A5, 0.5) | |
| =PERCENTILE(A1:A5, 0) |
=PERCENTILE(A1:A5, 0.5) ' 10 =PERCENTILE(A1:A5, 0) ' 0
The formula considers only the three numbers in the range: 0, 10, and 20. The
text in A2 and the empty cell A3 never enter the calculation, so the median of
(0,10,20) is 10. The second formula returns 0, which shows the opposite side of
the same rule: a cell containing the number zero is part of the data even
though an empty cell is not. If a range contains no numbers at all, both
functions return #NUM!.
PERCENTILE/QUARTILE Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| k is a percentage such as 90, or lies outside 0 to 1 | #NUM! error | Pass a fraction between 0 and 1, for example 0.9 for the 90th percentile |
| quart lies outside 0 to 4 | #NUM! error | Use an integer from 0 to 4; QUARTILE.EXC accepts only 1, 2, and 3 |
| k cannot be read as a number, such as the text "ninety" | #VALUE! error | Supply a numeric k, for example 0.9 |
| The range holds no numbers, only text, blanks, and logical values | #NUM! error | Make sure at least one numeric value is present |
| The range contains an error value | The same error is returned | Repair the source value, or keep errors out of the range with IFERROR |
| k is too close to 0 or 1 for the sample size in PERCENTILE.EXC | #NUM! error | Use a k between 1/(n + 1) and n/(n + 1), or switch to PERCENTILE.INC |
| quart is not a whole number, such as 2.9 | The value is truncated to quart 2 | Pass one of the whole numbers 0, 1, 2, 3, or 4 |
| Numbers are stored as text in the cells | Fewer numbers than expected, or #NUM! error | Convert the text to numbers, for example with VALUE |
The PERCENTILE and QUARTILE functions are the
standard way to locate a value inside a distribution in Excel. From a single
percentile of test scores to the interquartile bounds used for outlier
detection, they turn a raw list of numbers into statements about where a value
stands. Remember that k runs from 0 to 1 and not from 0 to 100, that quart runs
from 0 to 4, and that both functions interpolate linearly between neighboring
values instead of snapping to the nearest one. The original names behave
exactly like the .INC versions, which include the minimum and maximum of the
data, while the .EXC versions exclude those endpoints and also reject k values
that sit too close to them for the sample size. Text, logical values, and
empty cells inside a range are skipped silently while zeros count as real data,
so it pays to check that a range holds the numbers you expect. Mastering these
details makes every percentile and quartile in your reports dependable and easy
to explain.
Author
List all Excel Formulas.