How to calculate percentiles and quartiles in Excel

The top 10% cutoff with PERCENTILE.INC

The sales manager wants to know what it takes to be in the top 10% of reps this quarter. That is the 90th percentile: =PERCENTILE.INC(B2:B13,0.9). The second argument is a fraction between 0 and 1, so type 0.9 or 90%, not 90. Typing 90 returns #NUM!.

A percentile is usually not one of your numbers. Excel sorts the values, works out a position of (n - 1) x k + 1, and if that position falls between two values it takes the right share of the gap between them. With 12 reps the position is 11 x 0.9 + 1 = 10.9, so the answer sits 90% of the way from the 10th smallest value (27,300) to the 11th (31,200). The formula below does exactly that with SMALL, so you can see where the number comes from; in Excel, PERCENTILE.INC gives the same result in one step.

Who is in the top 10%, and why it is not 1.2 people

Count the reps at or above the cutoff with COUNTIF. In Excel you would write =COUNTIF(B2:B13,">="&PERCENTILE.INC(B2:B13,0.9)). Here the SMALL version stands in for PERCENTILE.INC.

10% of 12 reps is 1.2 people, which cannot happen, so the count will never match the percentage exactly on small lists. Decide what you really mean. If the rule is "the top 10% by value", use the percentile. If it is "the best 2 reps", use =LARGE(B2:B13,2) as the cutoff instead, which always returns a real sales figure and a fixed head count.

PERCENTILE.INC or PERCENTILE.EXC?

PERCENTILE.INC includes the ends: k can be anything from 0 to 1, and 0 returns the minimum, 1 the maximum. PERCENTILE.EXC excludes them. It uses the position (n + 1) x k, so the result spreads a little wider, and it returns #NUM! if k is too close to 0 or 1 for the amount of data. With 12 values, k must be between 1/13 and 12/13 (about 0.077 to 0.923), so =PERCENTILE.EXC(B2:B13,0.95) is #NUM! here.

For the 90th percentile, EXC uses position 13 x 0.9 = 11.7, which is 70% of the way from the 11th value to the 12th. The formula below writes that out. The gap between the two methods is large here because Jade's 38,900 sits far above everyone else; with hundreds of values the two give almost the same answer.

Pick INC unless you have a reason not to. It is what the older PERCENTILE function does, it works for any k, and it is what most spreadsheets and people expect. Use EXC only when a textbook, a standard or a colleague's analysis specifies the exclusive method, and then use it everywhere so your numbers match.

Quartiles: QUARTILE.INC and the interquartile range

Quartiles are just three particular percentiles. =QUARTILE.INC(B2:B13,1) is the 25th percentile (Q1), 2 is the median, and 3 is the 75th percentile (Q3). QUARTILE.INC also accepts 0 for the minimum and 4 for the maximum. QUARTILE.EXC only accepts 1, 2 and 3 and returns #NUM! for 0 or 4.

For Q1 the inclusive position is 11 x 0.25 + 1 = 3.75, so the formula below takes 75% of the way from the 3rd smallest value to the 4th. Q3 works the same way at position 9.25: =SMALL(B2:B13,9)+0.25*(SMALL(B2:B13,10)-SMALL(B2:B13,9)), which gives 26,250. Q3 minus Q1 is the interquartile range (IQR), the spread of the middle half of the team. Unlike the range from minimum to maximum, one huge quarter from Jade barely moves it.

If you build a Box and Whisker chart (Insert > Insert Statistic Chart > Box and Whisker), Format Data Series has a Quartile Calculation setting with Inclusive median and Exclusive median. Set it to match the formulas you report, or the chart and the table will disagree.

What percentile is one rep at? PERCENTRANK.INC

PERCENTRANK.INC answers the reverse question: given a value, where does it fall? =PERCENTRANK.INC(B2:B13,B10) for Ivan counts how many values are below his and divides by n - 1. The COUNTIF formula below does the same arithmetic.

Two details catch people out. First, PERCENTRANK.INC shows 3 significant digits by default and cuts off the rest rather than rounding, so 4/11 shows as 0.363, not 0.364. Add a third argument for more digits, such as =PERCENTRANK.INC(B2:B13,B10,4). Second, tied values get the same, lower rank: Ivan and Kofi both sold 21,400, so both get the rank of the first of the pair. PERCENTRANK.EXC divides by n + 1 instead, so it never returns exactly 0 or 1. If the value is below the smallest or above the largest number in the range, both return #N/A.

Percentile for one group only

There is no PERCENTILEIF. In Excel 365 or 2021, filter first: =PERCENTILE.INC(FILTER(C2:C50,B2:B50="West"),0.9) gives the 90th percentile for West reps only. In older versions, =PERCENTILE.INC(IF(B2:B50="West",C2:C50),0.9) does the same but must be confirmed with Ctrl+Shift+Enter. Blank cells and text in the range are ignored, but zeros count, so a rep with no sales entered as 0 drags every percentile down. Leave it empty if the rep was not active.