PERCENTILE.INC Function

Returns the value below which a given percentage of the data falls (0 to 1 inclusive).

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Taught hands-on in Data Analytics · Descriptive Statistics module

Overview

PERCENTILE.INC(range, 0.9) returns the 90th percentile — the value that 90% of the data is at or below. k can be anything from 0 (the minimum) to 1 (the maximum).

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.INC(array, k) Explain

Arguments

ArgumentRequired?What it does
arrayYesThe range of numbers.
kYesThe percentile, from 0 to 1 (0.9 or 90%).

Examples

Example 1 — Sales percentiles

Sorted, the sales are 39,800 · 48,500 · 48,500 · 50,000 · 55,400 · 62,000 · 71,250. The 90th percentile falls 40% of the way from 62,000 to 71,250.

E2fx=PERCENTILE.INC($C$2:$C$8,D2)
ABCDE
1RepRegionSaleskPercentile
2AnaNorth62000039800
3BenSouth485000.2548500
4CyNorth500000.550000
5DiEast712500.965700
6EdSouth39800171250
7FayNorth55400
8GusEast48500
=PERCENTILE.INC($C$2:$C$8,D2) Explain

Example 2 — Top-10% threshold

Flag reps at or above the 90th percentile.

=C2>=PERCENTILE.INC($C$2:$C$8,0.9) Explain

Common mistakes

  • #NUM! k is below 0 or above 1, or the range is empty.
  • Typing 90 instead of 0.9. k is a fraction. Use 0.9 or 90%.

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.