Description
Use this function to calculate the k-th percentile of values in a range, including the greatest and smallest values, where k is in the range 0 through 1, inclusive.
Use PERCENTILE.INC when you want to include all data points, including extremes.
Syntax
PERCENTILE.INC(array, k)
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
array |
Yes | The array or range of data that defines relative standing. | A number, a reference to a cell containing a number, a cell range, or a formula which results in any of these. |
k |
Yes | The percentile value in the range 0 to 1, inclusive. | A positive number between 0 and 1, inclusive, a reference to a cell containing such a number, or a formula which results in either of these. |
Example
Sample data
| A | B | |
|---|---|---|
1 |
Score | Student |
2 |
75 | Alice |
3 |
82 | Bob |
4 |
90 | Charlie |
5 |
68 | David |
6 |
95 | Eve |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Find the median score in a data set. | =PERCENTILE.INC(A2:A6, 0.5) |
Returns the value at the 50th percentile of the sorted scores. Result: 82 |
| Find the value marking the bottom 25% of a data set. | =PERCENTILE.INC(A2:A6, 0.25) |
Returns the value at the 25th percentile of the sorted scores. Result: 75 |
| Find the value marking the top 25% of a data set. | =PERCENTILE.INC(A2:A6, 0.75) |
Returns the value at the 75th percentile of the sorted scores. Result: 90 |
| Find the minimum value in a data set. | =PERCENTILE.INC(A2:A6, 0) |
Returns the smallest score in the data set. Result: 68 |
| Find the maximum value in a data set. | =PERCENTILE.INC(A2:A6, 1) |
Returns the largest score in the data set. Result: 95 |
Notes
- Functionally PERCENTILE.INC operates the same as PERCENTILE.
- PERCENTILE.INC includes both the lowest and highest values in the dataset.
- PERCENTILE.INC 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, PERCENTILE.INC 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.INC to such a split range, use a helper column (reference A1:A12 to B1:B12, and A16:A27 to B13:B23, and then usePERCENTILE.INC(B1:B23, k)to determine the desired percentile value. - If
kis not a number, or is outside the range of 0 through 1, PERCENTILE.INC returns the #NUM! error. - The k-th percentile is the value below which
kpercent of the data falls.
Tips
- Use PERCENTILE.INC when you need to find a value in a dataset that represents a specific percentile, especially when interpolation between data points is desired.
- PERCENTILE.INC treats the data as a continuous distribution, which may not be appropriate for all datasets.
- For large datasets, consider using PERCENTILE.EXC, which excludes the lowest and highest values, potentially providing a more robust measure.
- When working with integer-only data where interpolation isn't appropriate, consider using QUARTILE.INC instead.
- Use PERCENTILE.INC in conjunction with other statistical functions such as AVERAGE, STDEV.P, and MEDIAN for more comprehensive data analysis.