PERCENTILE.EXC function

Returns the k-th percentile of a dataset, excluding minimum and maximum values for sample-based statistical analysis.

=PERCENTILE.EXC(array, k)

Generate a PERCENTILE.EXC formula

Describe what you need. The generator will reach for PERCENTILE.EXC where PERCENTILE.EXC is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How PERCENTILE.EXC reads its arguments
arrayrequiredkrequiredPERCENTILE.EXC →
ArgumentRequiredDescription
arrayRequiredRequired. A range or array of numeric values; empty cells and text values are automatically ignored during calculation.
kRequiredRequired. A number strictly between 0 and 1 (exclusive); 0.25 for 25th percentile, 0.5 for median, 0.75 for 75th percentile.

Returns

A number representing the interpolated percentile value in the same units as the input array.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find the 75th percentile of units sold across all orders

Order IDRegionRepUnitsUnit PriceOrder Date
1001NorthAlice51202024-01-05
1002SouthBob31502024-01-08
1003EastCarol8952024-01-10
1004WestDave22002024-01-12
1005NorthAlice61302024-01-15
1006SouthBob41602024-01-18
1007EastCarol71052024-01-20
1008WestDave91102024-01-22
=PERCENTILE.EXC(B2:B9, 0.75)

Result: 7.75

The Units column contains [5, 3, 8, 2, 6, 4, 7, 9]. When sorted, these become [2, 3, 4, 5, 6, 7, 8, 9]. PERCENTILE.EXC calculates position 0.75 × (8+1) = 6.75, interpolating between the 6th and 7th values (7 and 8), yielding 7.75. This represents the 75th percentile of order volumes.

2. Find the 25th percentile of unit prices to identify budget products

Order IDRegionRepUnitsUnit PriceOrder Date
1001NorthAlice51202024-01-05
1002SouthBob31502024-01-08
1003EastCarol8952024-01-10
1004WestDave22002024-01-12
1005NorthAlice61302024-01-15
1006SouthBob41602024-01-18
1007EastCarol71052024-01-20
1008WestDave91102024-01-22
=PERCENTILE.EXC(E2:E9, 0.25)

Result: 106.25

Unit prices [120, 150, 95, 200, 130, 160, 105, 110] sort to [95, 105, 110, 120, 130, 150, 160, 200]. Position 0.25 × 9 = 2.25 falls between the 2nd value (105) and 3rd value (110). Linear interpolation: 105 + 0.25 × (110 − 105) = 106.25, marking the threshold where the cheapest 25% of products end.

3. Calculate the median revenue per order

Order IDRegionRepUnitsUnit PriceRevenue
1001NorthAlice5120600
1002SouthBob3150450
1003EastCarol895760
1004WestDave2200400
1005NorthAlice6130780
1006SouthBob4160640
1007EastCarol7105735
1008WestDave9110990
=PERCENTILE.EXC(I2:I9, 0.5)

Result: 687.5

Revenue totals [600, 450, 760, 400, 780, 640, 735, 990] sort to [400, 450, 600, 640, 735, 760, 780, 990]. The median (50th percentile) uses position 0.5 × 9 = 4.5, landing between the 4th value (640) and 5th value (735). Interpolation: 640 + 0.5 × (735 − 640) = 687.5, representing the true middle of the revenue distribution.

Common errors

Which PERCENTILE.EXC error are you seeing?
PERCENTILE.EXC returned an error#NUM!
Enter k as a decimal between 0 and 1 exclusive, such as 0.25, 0.5, or 0.75. To find min/max, use MIN() and MAX() functions instead.
#VALUE!
Remove quotes around k if present. Ensure all values in the array range are actual numbers; filter out text entries or empty rows that may contain text labels.
#NUM!
Expand the array range to include more numeric data, or switch to PERCENTILE.INC if you have only 1 value (though this is unusual for percentile analysis).
ErrorWhy it happensHow to fix it
#NUM!k is entered as 0, 1, or any value outside the range (0, 1). PERCENTILE.EXC requires k to be strictly between these boundaries, never equal to them.Enter k as a decimal between 0 and 1 exclusive, such as 0.25, 0.5, or 0.75. To find min/max, use MIN() and MAX() functions instead.
#VALUE!The array contains non-numeric text, or k is entered as a text string (e.g., '0.5' with quotes instead of 0.5 without quotes).Remove quotes around k if present. Ensure all values in the array range are actual numbers; filter out text entries or empty rows that may contain text labels.
#NUM!The array contains fewer than 2 numeric values after ignoring text and empty cells. PERCENTILE.EXC needs at least 2 points to interpolate.Expand the array range to include more numeric data, or switch to PERCENTILE.INC if you have only 1 value (though this is unusual for percentile analysis).

Tips and when to use something else

  • Use 0.25 for the 1st quartile, 0.5 for the median, and 0.75 for the 3rd quartile; these are the most common percentiles in business analysis.
  • PERCENTILE.EXC differs from PERCENTILE.INC in its calculation method: .EXC uses k×(n+1) position formula, excluding extreme values, making it ideal for sample-based data rather than entire populations.
  • For multiple percentile calculations on the same data, consider using QUARTILE.EXC or building a helper column instead of repeating PERCENTILE.EXC many times.
  • If you need to find values at the actual minimum or maximum, use MIN() and MAX() instead—PERCENTILE.EXC cannot return 0th or 100th percentiles by design.

Related functions

PERCENTILE.INCReturns the value at a specified percentile (0–100%) of a dataset using linear interpolation for in-between values.MEDIANMEDIAN returns the middle value in a sorted list of numbers, or the average of the two middle values if the count is even.LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.SMALLSMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.

Frequently asked questions

What is the difference between PERCENTILE.EXC and PERCENTILE.INC?
Both calculate the k-th percentile, but they use different formulas for position. PERCENTILE.INC uses k×(n−1), treating the data as a population, while PERCENTILE.EXC uses k×(n+1), treating it as a sample. On the same dataset, PERCENTILE.EXC returns values strictly within the data range, never at the exact minimum or maximum.
When should I use PERCENTILE.EXC instead of PERCENTILE.INC?
Use PERCENTILE.EXC when you're analyzing a sample drawn from a larger population, or when your analysis framework (like certain statistical methodologies) expects the exclusive method. Most modern statistical software defaults to PERCENTILE.EXC, and Excel/Sheets support both so you can match your source system.
Can I use PERCENTILE.EXC to calculate quartiles?
Yes. Set k=0.25 for Q1, k=0.5 for Q2 (median), and k=0.75 for Q3. If you need all quartiles plus min/max, the dedicated QUARTILE.EXC function is more concise, or manually combine MIN, PERCENTILE.EXC (three calls), and MAX.
How does PERCENTILE.EXC handle empty cells or text in the array?
Empty cells and text values are silently ignored; only numeric values participate in the percentile calculation. However, if removing these leaves fewer than 2 numeric values, the function returns #NUM!. Use COUNTA or similar to audit your array size before applying percentile calculations.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17