PERCENTILE

Returns a specified percentile in a list of numbers.

Syntax:

PERCENTILE(numberlist, fraction)

PERCENTILE returns the value below which fraction of the numbers in numberlist lie. fraction must be between 0 and 1 inclusive;

numberlist is a range or array of numbers, not necessarily in order.

PERCENTILE internally assigns a rank to each number in numberlist, where 0 is the rank of the lowest number, 1 of the next lowest and so on. The rank r of the value to be found is fraction * (N-1), where N is the count of numbers in numberlist. If r is integer the corresponding value from numberlist is returned; otherwise the value is calculated proportionately between the values with rank |r| and |r|+1 (see the examples).

This function may be useful for example when determining a score below which lies a certain percentage of all scores for a test. It is of limited use with a small list of numbers.

PERCENTILE returns the maximum, median, minimum value when fraction is 1, 0.5, 0 respectively.

Example:

PERCENTILE(A2:A99, 0.6)

where cells A2:A99 contain scores from a test, returns the score below which 60% of the scores lie.

The next two examples illustrate the calculation, but are not intended to show practical application:

PERCENTILE({3, 4, 9, 11, 12}, 0.5)

returns 9, the median value of the list.

PERCENTILE({9, 3, 7, 8}, 0.25)

returns 6. There are 4 numbers in the list. The rank of the value to be found is 0.25 * (4-1) = 0.75. The lowest number 3 is assigned rank 0; the next highest number 7 is assigned rank 1. The value with rank 0.75 lies proportionately between 3 and 7, and is calculated as 3 + (7-3)*0.75 = 6.

Application:

Employee Salary Analysis

Imagine you are a Human Resources manager and you want to understand how a specific employee's salary compares to the rest of the company. You can use the PERCENTILE function to find out.

Scenario: We have a list of annual salaries for 15 employees in a small company.

Employee Salary Data Table:

	Employee ID	Annual Salary
	A	B
1	101	$55,000.00
2	102	$60,000.00
3	103	$62,500.00
4	104	$65,000.00
5	105	$70,000.00
6	106	$72,000.00
7	107	$75,000.00
8	108	$78,000.00
9	109	$80,000.00
10	110	$82,000.00
11	111	$85,000.00
12	112	$90,000.00
13	113	$95,000.00
14	114	$110,000.00
15	115	$125,000.00

Objective: Let's say we want to find the salary value that represents the 75th percentile of all salaries in the company. This would tell us the salary at which 75% of the employees are paid less than or equal to that amount.

Using the PERCENTILE function:

The function takes two arguments: the data set and the percentile value (as a decimal).

PERCENTILE(Salaries, 0.75)

Steps to find the 75th Percentile:

Calculate the position: The position of the 75th percentile is calculated as: Position=k×(n−1)+1 Where:
Position=0.75×(15−1)+1 Position=0.75×14+1 Position=10.5+1 Position=11.5
- k is the percentile (0.75)
- n is the number of data points (15)
Find the value: Since the position is not a whole number, we need to interpolate between the values at the 11th and 12th positions in our sorted list.
The value is halfway between these two salaries (since the position is 11.5).
- 11th position salary: $85,000
- 12th position salary: $90,000
- 85,000+(90,000−85,000)×0.5
- 85,000+5,000×0.5
- 85,000+2,500=87,500

Result: The 75th percentile salary is $87,500.

Interpretation:

This means that 75% of the employees in the company earn an annual salary of $87,500 or less.

Conclusion:

Compensation Analysis: An HR manager can use this to ensure that employee salaries are competitive and fair. If a new employee is hired at a salary below the 25th percentile, it might be considered a low offer.
Performance Reviews: When considering a salary increase for an employee, the percentile can be a benchmark. For example, a high-performing employee might be brought up to the 75th percentile to reflect their contribution.
Budgeting: It helps in understanding the distribution of salary costs and can inform future budget planning for payroll.

Result for PERCENTILE(Salaries, 0.75):

$87,500.00