PERCENTILE.EXC
A percentile of a column's values, k strictly between 0 and 1.
Group: Statistics. Compatible with DAX.
Syntax
PERCENTILE.EXC ( <column>, <k> )| Parameter | Type | Description |
|---|---|---|
column | Column | A column of numbers. |
k | Number | The share of values below the result, from 1/(n + 1) to n/(n + 1) for n values. |
Returns
Number. The k-th percentile of the column's values by the exclusive method, blanks left out.
Remarks
- The exclusive method sets the n sorted values at k = 1/(n + 1), 2/(n + 1) … n/(n + 1) and draws straight lines between them. A k outside that range is an error: with 4 values, 0.1 is too small.
- Compared with
PERCENTILE.INCit reaches further out at the ends; in the middle the two differ little. - Blank cells are left out. Dates, text and TRUE/FALSE are errors; with no value the result is BLANK.
Examples
Inclusive and exclusive on four prices
Prices of 30, 38, 52 and 64 per liter.
EVALUATE
ROW (
"PERCENTILE.EXC", PERCENTILE.EXC ( Products[Price per liter], 0.25 ),
"PERCENTILE.INC", PERCENTILE.INC ( Products[Price per liter], 0.25 )
)| PERCENTILE.EXC | PERCENTILE.INC |
|---|---|
| 32 | 36 |
Blanks are left out
Two of five holdings have no price: the median is the middle of three.
EVALUATE
ROW ( "Median price", PERCENTILE.EXC ( Holdings[Price], 0.5 ) )| Median price |
|---|
| 97.84 |