Excel like a finance pro.
← The libraryPractice · 2 questions →
Statistical function
PERCENTILE.EXC
Returns the value at a percentile, excluding 0 and 1.
Rarely usedDifficulty 1300 · ProficientUsage rank #308 of 520
When to use it
Percentile using the exclusive method, which treats the data as a sample from a larger population. k must be between 1/(n+1) and n/(n+1).
The shape of it
- Syntax
=PERCENTILE.EXC(array, k)
Worked examples
First quartile
=PERCENTILE.EXC({1,2,3,4,5},0.25) → 1.5
Position 0.25 × 6 = 1.5.
Median
=PERCENTILE.EXC({1,2,3,4,5},0.5) → 3
The median is the same under both methods.
Out of range
=PERCENTILE.EXC({1,2,3,4,5},0.1) → #NUM!
Position 0.6 is before the first value.
Worth knowing
- Matches the default in Minitab and some statistics courses; PERCENTILE.INC matches most BI tools.
- Use it when the extremes of a small sample should not be treated as the true extremes.
- Works best with 20 or more data points.
Where it goes wrong
- #NUM! for small k or 1-k on small samples.
- Results differ from PERCENTILE.INC, which surprises people comparing two reports.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.