percentile
Returns the nth percentile for a set of numbers.
Don't confuse percentile with percentage. A percentile is the value below which a certain percentage of the data in a dataset is found. For example, the 20th percentile is the value below which 20% of the dataset is found.
The percentile might not be a value in the input data. For example, if you want to calculate the 50th percentile of the dataset (1, 2, 3, 4, 5, 6), there is no value in the set below which 50% of the data can fall. The percentile is 3.5 — that is, the midway point between 3 and 4.
Parameters
INPUT LIST (list or table region)
A list or table region of numbers.
PERCENTILE (number)
The target percentile. Must be a number greater than zero and either less than or equal to 100.
IGNORE NULLS (Boolean)
Optional. Whether to ignore null/empty values (true) or count them as zero (false).
Default: true
Examples
ATL in Script | Result | Notes |
|---|---|---|
| 4 | Returns the 50th percentile. |
| 4 | The function sorts the input list before calculating. |
| 2.75 | The input list can contain decimal numbers. |
HANDLING NULL OR EMPTY VALUES
Assume a "Describe the Table" project with this data:
Employee_ID | Age | |
|---|---|---|
Row 1 | 0001 | 35 |
Row 2 | 0002 | |
Row 3 | 0003 | 41 |
Row 4 | 0004 | |
Row 5 | 0005 | 35 |
Row 6 | 0006 | |
Row 7 | 0007 | 57 |
Row 8 | 0008 | 23 |
The first parameter can take a column variable, but here the Age column has several empty values. The optional third parameter tells the function to ignore the null/empty values or count them as zeros. For example:
ATL in Script | Result | Notes |
|---|---|---|
| 35 | The IGNORE NULLS parameter is unspecified, so true applies by default. The empty values are ignored. |
| 29 | The IGNORE NULLS parameter is set to false. Each empty value in the Age column is counted as a zero. |
Important
In a "Describe Row in Context" project, you would use AgeColumn for the input variable.
