Description
Use this function to calculate 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 |
|---|
| Values |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=PERCENTILE.EXC(A2:A11, 0.25) |
Returns the 25th percentile of the data set | 3.25 |
=PERCENTILE.EXC(A2:A11, 0.5) |
Returns the 50th percentile (median) of the data set | 5.5 |
=PERCENTILE.EXC(A2:A11, 0.75) |
Returns the 75th percentile of the data set | 7.75 |
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.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA