fx
FunctionPedia
Quick Answer

PERCENTILE.EXC Function

Returns the value at percentile k (0 < k < 1) using the exclusive method.

✓ Excel✓ Google SheetsExcel Excel 2010+

Syntax

PERCENTILE.EXC
(array, k)

Parameters

ParameterDescriptionRequired
arrayThe range or array of numeric values.Required
kThe percentile, a number strictly between 0 and 1 (e.g. 0.25, 0.75).Required

Basic Example

25th percentile of six values

=PERCENTILE.EXC({1,2,3,4,5,6}, 0.25)
Result1.75

Rank = 0.25*(6+1) = 1.75; interpolate 75% from the 1st to the 2nd value: 1 + 0.75*(2-1) = 1.75.

Advanced Examples

Example 1: 75th percentile of six values

Upper quartile via exclusive method

The exclusive rank places the percentile inside the data range.

=PERCENTILE.EXC({1,2,3,4,5,6}, 0.75)
Result: 5.25
Rank = 0.75*(6+1) = 5.25; interpolate 25% from the 5th to the 6th value: 5 + 0.25*(6-5) = 5.25.

Example 2: k at the edge is rejected

Why k = 0 or 1 fails

The exclusive method requires k strictly between 0 and 1, so the extremes are invalid.

=PERCENTILE.EXC({1,2,3,4,5,6}, 0)
Result: #NUM!
k = 0 is not allowed; the exclusive method cannot return the minimum.

How PERCENTILE.EXC Works

PERCENTILE.EXC uses the exclusive (EXC) method. It computes a rank as k*(n+1), where n is the count of values, then linearly interpolates between the surrounding sorted values. Because k is strictly between 0 and 1, the result always lies strictly between the minimum and maximum. k must fall in the open interval (1/(n+1), n/(n+1)); values outside this range return #NUM!. Text and logical values are ignored.

1
Select the result cell
Click where you want the percentile.
2
Enter the formula
Type =PERCENTILE.EXC(range, k) with k strictly between 0 and 1.
3
Press Enter
The interpolated percentile value is returned.

Important Notes & Limitations

  • k must be strictly between 0 and 1; k = 0 or k = 1 cause #NUM!.

  • Only certain k values are valid; k must be in (1/(n+1), n/(n+1)) or #NUM! occurs.

  • Interpolates, so the result is generally not an actual data value.

Common Errors & Fixes

#NUM! errork is 0 or 1, or k is outside the valid range (1/(n+1), n/(n+1)).

Fix: Use a k strictly between 0 and 1, or switch to PERCENTILE.INC if you need the minimum or maximum.

Result differs from PERCENTILE.INCThe two functions use different interpolation formulas.

Fix: Choose .INC when you need inclusive 0..1 bounds; .EXC otherwise.

Download Practice File

Practice PERCENTILE.EXC with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the PERCENTILE.EXC function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

What is the difference between .INC and .EXC?
.INC allows k from 0 to 1 and uses rank 1 + k*(n-1), potentially returning the min or max. .EXC requires k strictly between 0 and 1 and uses rank k*(n+1), so it never returns the min or max.
Why do I get #NUM! with k = 0.1 on a small list?
On a small dataset the valid range (1/(n+1), n/(n+1)) may exclude small k; for n=6, k must be above 1/7 (~0.143).
When should I use PERCENTILE.EXC?
Use it when your statistical definition requires excluding the endpoints, such as certain textbook percentile formulas.