Cellwise Start free

Statistics function

Excel PERCENTILE.INC function

PERCENTILE.INC returns the value below which a given share of your data falls. PERCENTILE.INC(range,0.9) is the 90th percentile, a common cutoff for the top 10%. The result is often not one of your numbers: Excel interpolates between the two values around the position it needs.

=PERCENTILE.INC(array, k)

Arguments

array
The range of numbers. Text and empty cells are skipped.
k
The percentile as a fraction from 0 to 1: 0.9 or 90% for the 90th percentile. A list such as {0.25,0.5,0.75} returns several in Excel 365.

PERCENTILE.INC examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

AB
1OrderValue ($)
2SO-201120
3SO-20285
4SO-203240
5SO-20460
6SO-205150
7SO-20695
8SO-207310
9SO-208130
10SO-20975
11SO-210180

Download this sheet as an Excel practice file (.xlsx)

  1. =PERCENTILE.INC(B2:B11,0.9)

    Result: 247

    The 90th percentile is 247. Sorted, the values sit at positions 0 to 9; 0.9 x 9 = 8.1, so the result is 10% of the way from 240 to 310.

  2. =PERCENTILE.INC(B2:B11,{0.25,0.5,0.75})

    Result: spills 1 row × 3 columns

    87.5125172.5

    A list of k values spills the first quartile, median and third quartile: 87.5, 125 and 172.5.

  3. =COUNTIF(B2:B11,">="&PERCENTILE.INC(B2:B11,0.9))

    Result: 1

    Only one order, the $310 one, is at or above the 90th percentile. 10% of ten orders is one order.

  4. =PERCENTILE.INC(B2:B11,90)

    Result: #NUM!

    k must be between 0 and 1. Typing 90 instead of 0.9 returns #NUM!.

How to use PERCENTILE.INC

  1. Click the cell for the result.
  2. Type =PERCENTILE.INC( and select the numbers.
  3. Type a comma and the percentile as a fraction, such as 0.9.
  4. Press Enter.

Common PERCENTILE.INC mistakes

Typing 90 for the 90th percentile

k is a fraction. Use 0.9 or 90%; anything above 1 or below 0 returns #NUM!.

Expecting one of your own values

Between two values PERCENTILE.INC interpolates, so the 90th percentile here is 247 even though no order is worth 247. Use LARGE or SMALL if you need a real value.

Zeros for missing data

Zeros count as values and pull every percentile down. Leave a missing value empty so it is skipped.

PERCENTILE.INC FAQ

What is the difference between PERCENTILE.INC and PERCENTILE.EXC?

INC includes the ends: k can be 0 (the minimum) to 1 (the maximum). EXC excludes them and returns #NUM! for k too close to 0 or 1 for the amount of data. On small lists the two give noticeably different answers.

Is PERCENTILE the same as PERCENTILE.INC?

Yes. PERCENTILE is the older name and returns the same result.

How do I find the top 10% in Excel?

Get the cutoff with =PERCENTILE.INC(range,0.9), then flag values with =B2>=cutoff or count them with COUNTIF(range,">="&cutoff).

How do I calculate a percentile for one group only?

In Excel 365 use =PERCENTILE.INC(FILTER(B2:B11,A2:A11="East"),0.9). In older versions use PERCENTILE.INC(IF(...)) with Ctrl+Shift+Enter.

Guides that use PERCENTILE.INC

Related functions

Updated