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 |
| 2 | 15 |
| 3 | 20 |
| 4 | 35 |
| 5 | 40 |
| 6 | 50 |
| A | B | C | D |
|---|---|---|---|
| 1 | Percentile | Result | Formula |
| 2 | 0.15 | #NUM! | =PERCENTILE.EXC(A2:A6, B2) |
| 3 | 0.4 | 26 | =PERCENTILE.EXC(A2:A6, B3) |
| 4 | 0.5 | 35 | =PERCENTILE.EXC(A2:A6, B4) |
| 5 | 0.8 | 48 | =PERCENTILE.EXC(A2:A6, B5) |
| 6 | 0.9 | #NUM! | =PERCENTILE.EXC(A2:A6, B6) |
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.