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 |
|---|---|
| Score | Student |
| 75 | Alice |
| 82 | Bob |
| 90 | Charlie |
| 68 | David |
| 95 | Eve |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=PERCENTILE.INC(A2:A6, 0.5) |
Calculates the 50th percentile (median) of the scores. | 82 |
=PERCENTILE.INC(A2:A6, 0.25) |
Calculates the 25th percentile of the scores. | 71.5 |
=PERCENTILE.INC(A2:A6, 0.75) |
Calculates the 75th percentile of the scores. | 92.5 |
=PERCENTILE.INC(A2:A6, 0) |
Calculates the 0th percentile (minimum) of the scores. | 68 |
=PERCENTILE.INC(A2:A6, 1) |
Calculates the 100th percentile (maximum) of the scores. | 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.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA