Summary

The Excel PERCENTILE.INC function calculates the "kth percentile" for a set of data where k is 0 to 1, inclusive. A percentile is a value below which a given percentage of values in a data set fall. You can use PERCENTILE.INC to determine the 90th percentile, the 80th percentile, etc.

Purpose 

Get kth percentile

Return value 

Calculated percentile for k

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).

Difference between PERCENTILE.INC and PERCENTILE.EXC

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.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.