Description
Use this function to calculate a percentile of a set of values. Supported in Chains.
PERCENTILE returns the k-th percentile of values in a range, where k is in the range 0 to 1, inclusive.
Syntax
PERCENTILE(array, k)
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
array |
Yes | The range of data to be considered. | A number, a reference to a cell containing a number, a cell range that includes numbers, or a formula which results in any of these. |
k |
Yes | The percentile value. | A positive number in the range between 0 and 1, inclusive. |
Example
Sample data
| A | B | ||
|---|---|---|---|
| 1 | Score | Student | |
| 2 | 65 | Alice | |
| 3 | 70 | Bob | |
| 4 | 80 | Charlie | |
| 5 | 85 | David | |
| 6 | 90 | Eve |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the value marking the top 25% of a data set. | =PERCENTILE(A2:A6, 0.75) |
Returns the value at the 75th percentile of the sorted scores. Result: 85 |
| Find the median score in a data set. | =PERCENTILE(A2:A6, 0.5) |
Returns the value at the 50th percentile of the sorted scores. Result: 80 |
| Find the value marking the bottom 25% of a data set. | =PERCENTILE(A2:A6, 0.25) |
Returns the value at the 25th percentile of the sorted scores. Result: 70 |
Notes
- PERCENTILE operates functionally the same as PERCENTILE.INC.
- PERCENTILE includes both the lowest and highest values in the dataset.
- PERCENTILE always returns the k-th percentile, even when
k < 1/(n-1)ork > (n-1)/n.
The k-th percentile is the value below whichkpercent of the data falls. - PERCENTILE uses linear interpolation between closest ranks to determine percentile values.
For example, if the 30th percentile actually falls between two data points, it calculates a value that lies between those two points using the formula:(n-1)k + 1, where n is the number of values. -
arraycan reference more than one column. - Non-numeric values in
arrayare ignored. - If
arraycontains no values, or contains more than 2M data points, PERCENTILE returns the #NUM! error - If
arrayis provided as a cell range, then all values must be adjacent (that is, "A1:A27", not "A1:A12, A16:A27"). If you need to apply PERCENTILE to such a split range, use a helper column (reference A1:A12 to B1:B12, and A16:A27 to B13:B23, and then usePERCENTILE(B1:B23, k)to determine the desired percentile value. - If
kis not a number, or is outside the range of 0 through 1 inclusive, PERCENTILE returns the #NUM! error.
Tips
- Use PERCENTILE when you want to find a value that falls at a certain percentile within a dataset.
- PERCENTILE is often used in statistical analysis to understand the distribution of data.
- For large datasets, consider using PERCENTILE.INC or PERCENTILE.EXC for more precise calculations.
- Remember that PERCENTILE treats its second argument as a percentage in decimal form (For example, 0.75 indicates the 75th percentile).
- You can combine PERCENTILE with other statistical functions such as AVERAGE or STDEV for comprehensive data analysis.