The PERCENTILE.EXC function returns the value at a given percentile of a dataset, exclusive of 0 and 1.
Parts of a PERCENTILE.EXC function
PERCENTILE.EXC(data, percentile)
| Part | Description |
data
|
The array or range containing the dataset to consider. |
percentile
|
The percentile whose value within data will be calculated and returned. |
Sample formulas
PERCENTILE.EXC(A2:A100,0.33)
PERCENTILE.EXC(A2:A100,A2)
Notes
- The value returned by
PERCENTILE.EXCis not necessarily a member of data as this function interpolates between values to calculate the alpha percentile. - The percentile must be between 0 and 1, exclusive.
- Different from
PERCENTILE.INC,PERCENTILE.EXCcalculates percentile based on a percentile range exclusive of 0 and 1.
Examples
| A | B | |
|---|---|---|
|
1
|
data | percentile |
|
2
|
15 | 0.15 |
|
3
|
20 | 0.4 |
|
4
|
35 | 0.5 |
|
5
|
40 | 0.8 |
|
6
|
50 | 0.9 |
| A | B | C | D | |
|---|---|---|---|---|
|
1
|
Percentile | Result | Formula | Explanation |
|
2
|
0.15 | #NUM! | =PERCENTILE.EXC(A2:A6, B2)
|
Returns #NUM! error because it can't interpolate. |
|
3
|
0.4 | 26 | =PERCENTILE.EXC(A2:A6, B3)
|
Executes as intended. |
|
4
|
0.5 | 35 | =PERCENTILE.EXC(A2:A6, B4)
|
Executes as intended. |
|
5
|
0.8 | 48 | =PERCENTILE.EXC(A2:A6, B5)
|
Executes as intended. |
|
6
|
0.9 | #NUM! | =PERCENTILE.EXC(A2:A6, B6)
|
Returns #NUM! error because it can't interpolate. |
Related functions
- PERCENTILE : Returns the value at a given percentile of a dataset.
- QUARTILE : Returns a value nearest to a specified quartile of a dataset.
- MEDIAN : Returns the median value in a numeric dataset.
- QUARTILE.EXC : The QUARTILE.EXC function returns value nearest to a given quartile of a dataset, exclusive of 0 and 4.
