Purpose
Return value
Syntax
=PERCENTILE.INC(array,k)- array - Data values.
- k - Number representing kth percentile.
How to use
The Excel PERCENTILE.INC function calculates the "kth percentile" for a set of data, where k is between 0 and 1, inclusive. A percentile is a value below which a given percentage of values in a data set fall. A percentile calculated with .4 as k means 40% percent of values are less than or equal to the calculated result, a percentile calculated with k = .9 means 90% percent of values are less than or equal to the calculated result.
To use PERCENTILE.INC, provide a range of values and a number between 0 and 1 for the "k" argument, which represents percent. For example:
=PERCENTILE.INC(range,.4) // 40th percentile
=PERCENTILE.INC(range,.9) // 90th percentile
You can also specify k as a percent using the % character:
=PERCENTILE.INC(range,80%) // 80th percentile
PERCENTILE.INC returns a value greater than or equal to the specified percentile.
In the example shown, the formula in G5 is:
=PERCENTILE.INC(scores,E5)
where "scores" is the named range C5:C14.
PERCENTILE.INC vs. PERCENTILE.EXC
PERCENTILE.INC includes the full range of 0 to 1 as valid k values, compared to PERCENTILE.EXC which excludes percentages below 1/(N+1) and above N/(N+1).
Note: Microsoft classifies PERCENTILE as a "compatibility function", now replaced by the PERCENTILE.INC function.
Notes
- k can be provided as a decimal (.5) or a percentage (50%)
- k must be between 0 and 1, otherwise PERCENTILE.INC will return the #NUM! error.
- When percentiles fall between values, PERCENTILE.INC will interpolate and return an intermediate value.
Related functions
PERCENTILE.EXC Function
The Excel PERCENTILE.EXC function calculates the "kth percentile" for a set of data where k is 0 to 1. A percentile is a value below which a given percentage of values in a data set fall. You can use PERCENTILE.EXC to determine the 90th percentile, the 80th percentile, etc.
SMALL Function
The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.
LARGE Function
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.
RANK Function
The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest (i.e. top sales) as well as smallest to largest (i.e. fastest time).
