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.