Description
Use this function tocalculate the k-th percentile of values in a range excluding the greatest and smallest values, where k is in the range 0 to 1, exclusive.
Use this when you want to exclude outliers at the very top and bottom of your dataset.
Syntax
PERCENTILE.EXC(array, k)
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
array |
Yes | The array or range of data that you want to calculate the percentile for. | A range of cells containing numeric values. |
k |
Yes | The percentile value to return, where k is greater than 0 and less than 1. | A real number between 0 and 1, exclusive. |
Example
Sample data
A |
||
|---|---|---|
1 |
Values |
|
2 |
42 |
|
3 |
17 |
|
4 |
89 |
|
5 |
3 |
|
6 |
65 |
|
7 |
24 |
|
8 |
91 |
|
9 |
58 |
|
10 |
73 |
|
11 |
12 |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the value marking the bottom 25% of a data set, excluding the extremes. | =PERCENTILE.EXC(A2:A11, 0.25) |
Interpolates between the 2nd and 3rd values (2 and 3) in the sorted data set to find the 25th percentile. Result: 15.75 |
| Find the middle value (median) of a data set, excluding the extremes. | =PERCENTILE.EXC(A2:A11, 0.5) |
Averages the 5th and 6th values (5 and 6) in the sorted data set to find the 50th percentile. Result: 50 |
| Find the value marking the top 25% of a data set, excluding the extremes. | =PERCENTILE.EXC(A2:A11, 0.75) |
Interpolates between the 8th and 9th values (8 and 9) in the sorted data set to find the 75th percentile. Result: 77 |
Notes
- PERCENTILE.EXC excludes the lowest and highest values in the dataset.
- PERCENTILE.EXC uses linear interpolation between closest ranks to determine percentile values.
For example, if the 30th percentile actually falls between two data points, it calculates a value that lies between those two points using the formula: (n-1)k + 1, where n is the number of values. -
arraycan reference more than one column. - Non-numeric values in
arrayare ignored. - If
arraycontains no values, PERCENTILE.EXC returns the #NUM! error - If
arrayis provided as a cell range, then all values must be adjacent (that is, "A1:A27", not "A1:A12, A16:A27"). If you need to apply PERCENTILE.EXC to such a split range, use a helper column (reference A1:A12 to B1:B12, and A16:A27 to B13:B23, and then usePERCENTILE.EXC(B1:B23,k)to determine the desired percentile value. - If
kis not a number, or is outside the range of 0 through 1, PERCENTILE.EXC returns the #NUM! error. - The k-th percentile is the value below which
kpercent of the data falls.
Tips
- Use PERCENTILE.EXC to determine specific percentiles in your data set for detailed analysis.
- When working with integer-only data where interpolation isn't appropriate, consider using QUARTILE.INC instead.
- PERCENTILE.EXC treats the data as a continuous distribution, which may not be appropriate for all datasets.
- Use PERCENTILE.EXC in conjunction with other statistical functions such as AVERAGE, STDEV.P, MEDIAN, and QUARTILE.EXC for more comprehensive data analysis.