PERCENTILE.EXC

Calculates the k-th percentile of values in an array or range, where k must be between 0 and 1, but it cannot be 0 or 1 itself.

Syntax:

PERCENTILE.EXC(array, k)

array is required, and is the array or range of cells you want to use to find the percentile.

k is required, and is the the percentile that you want to find.

Example:

If array, found in A1:A10, contains numbers 85, 92, 78, 90, 88, 75, 95, 80, 82, 70 and k, found in B1, contains 0.75:

PERCENTILE.EXC(A1:A10, B1)

returns 90.5

	A	B	C
1	85	0.75	90.5
2	92
3	78
4	90
5	88
6	75
7	95
8	80
9	82
10	70

Application:

Employee Performance Ranking

Imagine a company wants to evaluate the performance of its sales team based on the number of deals they closed in the last quarter. The management wants to identify the number of deals that represents the top 10% of performers, so they can award a bonus to those who exceed this threshold.

Here's the data for 20 sales representatives:

	Sales Rep	Deals Closed
	A	B
1	Ava Jackson	25
2	Ben Carter	31
3	Chloe Davis	18
4	Daniel Evans	40
5	Emma Foster	22
6	Felix Harris	35
7	Grace Hill	29
8	Henry Irving	45
9	Isabella Jones	15
10	Jack King	28
11	Kevin Lee	33
12	Lily Miller	38
13	Mason Nelson	20
14	Nora Owens	26
15	Oliver Perez	30
16	Paige Quinn	42
17	Ryan Bell	24
18	Samantha Watson	36
19	Thomas Clark	19
20	Victoria White	27

The company wants to find the 90th percentile to determine the cutoff for the top performers. We will use the PERCENTILE.EXC function.

Formula: PERCENTILE.EXC(B1:B20, 0.9)

B1:B20 is the array of deals closed.
0.9 is the k value, representing the 90th percentile.

Result:

The result of the PERCENTILE.EXC function is 41.8.

Conclusion:

Based on the 90th percentile calculation, any sales representative who closed more than 41.8 deals is considered to be in the top 10% of the team. In this example, sales representatives Henry Irving (45 deals) and Paige Quinn (42 deals) would qualify for the bonus. This illustrates how PERCENTILE.EXC provides a precise threshold for performance evaluation, excluding the minimum and maximum values from the calculation's boundary conditions.

Result for PERCENTILE.EXC(A1:A20, 0.9):

41.8