Percentile 1 method

This describes the algorithm of percentile1 method of finding percentile value in the dataset. The method is used by PERCENTILE or PERCENTILE.INC functions in Microsoft Excel and PERCENTILE Google Docs Sheets.

The function receives two parameters:

1. values: array of values in the dataset.
2. percentile: percentile value between 0 and 1 inclusive for which the percentile value will be calculated.

For example, this is how we calculate the 40th percentile for the following measurements: 35, 20, 50, 40, 15. Note that we pass the percentile 40 as a decimal value 0.4.

Sigma.percentile1(values: [35, 20, 50, 40, 15], percentile: 0.4)
// Result: 29

Here is the algorithm used for the calculation:

1) Sort the dataset

First, we sort the dataset from lowest to highest values.

sorted_values = [15, 20, 35, 40, 50]

2) Find the rank

Next, we find the rank of 40th percentile. Rank is the position of an element in the dataset. For example, rank 1 is the first element, 2 is the second and N is the last element. This value can be a decimal. For example 3.35 will be used to find the value between the third and fourth elements.

Equation 1

rank = percentile * (N - 1) + 1    

Where:

• percentile is the percentile argument of the function.
• N is the size of the dataset which is equal to the size of the array passed as values argument.

We substitute the arguments into Equation 1:

rank = 0.4 * (5 - 1) + 1 = 2.6

3) Get the integer and fractional part of the rank

The integer part of 2.6 is 2, this corresponds to the The fractional part of 2.6 is 0.6.

rank_integer = 2
rank_fraction = 0.6

4) Find the element at rank

Now we look the ordered dataset and find the element for rank_integer and rank_integer + 1. In our example, rank_integer is 2, so we need to find the second and third elements:

element_value =  sorted_values[rank_integer]
element_plus_one_value =  sorted_values[rank_integer + 1]