|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

PERCENTILE

PERCENTILE

  • Last updated on

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) or k > (n-1)/n.
    The k-th percentile is the value below which k percent 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.
  • array can reference more than one column.
  • Non-numeric values in array are ignored.
  • If array contains no values, or contains more than 2M data points, PERCENTILE returns the #NUM! error 
  • If array is 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 use PERCENTILE(B1:B23, k) to determine the desired percentile value.
  • If k is 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.

 

Related functions

DescriptionUse this function to calculate a percentile of a set of values. Supported in Chains.PERCENTILE returns the...