PERCENTILE.EXC Function

Returns the k-th percentile, with k strictly between 0 and 1.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Want to practice PERCENTILE.EXC in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

PERCENTILE.EXC finds percentiles using the (n + 1) method common in statistics packages. With n values, k must be between 1/(n+1) and n/(n+1).

The .INC version includes the end points (0 and 1, the smallest and largest values); the .EXC version excludes them and needs k strictly between them, so it can't reach the minimum or maximum. Both interpolate between values when k falls between two data points.

Syntax

=PERCENTILE.EXC(array, k) Explain

Arguments

ArgumentRequired?What it does
arrayYesThe range of numbers.
kYesThe percentile, strictly between 0 and 1.

Examples

Example 1 — Sales percentiles (exclusive)

With 7 values, k must be from 1/8 (0.125) to 7/8 (0.875), so 0.1 is out of range.

E2fx=PERCENTILE.EXC($C$2:$C$8,D2)
ABCDE
1RepRegionSaleskPercentile
2AnaNorth620000.1#NUM!
3BenSouth485000.2548500
4CyNorth500000.550000
5DiEast712500.865700
6EdSouth39800
7FayNorth55400
8GusEast48500
=PERCENTILE.EXC($C$2:$C$8,D2) Explain

Common mistakes

  • #NUM! k is 0, 1, or too close to either end for the amount of data.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.